What is a circular reference

A circular reference refers to a formula, that visits its own or another cell more than once in its chain of calculations, creating an infinite loop which slows down your spreadsheet significantly.

A circular reference in Excel indicates that the calculation in a certain cell refers to it’s own result once or several times. This is usually unintended.

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:

Manual detection of 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.

Intended circular references

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.

Hidden circular references

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.