Excel glossary

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 endless 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:

Example of a sum in Excel // PerfectXL Spreadsheet ValidationHowever, 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:

Example of a circular reference in Excel // PerfectXL Spreadsheet ValidationThis is not possible of course. Excel warns us for one or more circular references:

Excel warning for one or more circular references in a spreadsheet // PerfectXLAfter clicking ‘OK’ in the notification pop up, value ‘0’ is published in cel B7:

Example of a faulty result of a circular reference // PerfectXL

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.

Automatic Excel detection of circulare references // PerfectXL

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.

Read more about the different types of circular references and how to easily detect all kinds using PerfectXL.