Example of a Circular Reference
In the example below, in cell B7, we find the sum of fruit sales (cell range B2:B6). There is nothing wrong with this calculation:
However, if we’d accidentally select cell range B3:B7, in stead of B2:B6, we’d be asking the function in cell B7 to perform a calculation that includes the result of this exact calculation:
This is not possible of course. Excel warns us for one or more circular references:
After clicking ‘OK’ in the notification pop up, value ‘0’ is published in cel B7:
Manually detect Circular References in Excel
Imagine receiving a spreadsheet from a co-worker. You want to make sure there are no circular references in the file. Go to tab ‘Formulas’, choose ‘Error-checking’ and ‘Circular References’. Excel will show you exactly in which cell(s) circular references are detected.
Different types of Circular References
Most circular references are unintended: mistakes. Excel will easily trace these instances for you.
There are also intended circular references; these are usually made by very experienced Excel users. Intended circular references can be used to make iterative calculations.
Last but not least, there are hidden circular references. These are very dangerous, because they are hard to detect. For example, because the operation of such a circular reference is dependent on the value of another cell.