How Do I Check for Circular References in Excel?

In Excel, a circular reference occurs when a cell refers to itself in a formula, such as if cell E7 is set to calculate the sum of cells E1 through E7. Working out such a formula would cause an endless loop, so instead of performing the calculation, Excel displays a warning. Unlike some formula errors, however, Excel doesn't force you to immediately fix the problem, which can lead to forgotten and lost circular references breaking your workbook. To check for these problems, look in the "Error Checking" drop-down menu.

...
Cells with circular references display the number 0.
credit: Image courtesy of Microsoft

Step

Switch to the "Formulas" tab in Excel 2013 or 2010 and click "Error Checking" in the Formula Auditing section to scan the current worksheet for formula errors. Error checking looks for several types of mistakes, such as division by zero, which might lead to further problems while trying to fix circular references.

...
Run an error check.
credit: Image courtesy of Microsoft
...
Check the circular reference list.
credit: Image courtesy of Microsoft

Step

Open the drop-down menu next to the "Error Checking" button and hover over "Circular References" to see a list of cells containing circular references. Click one to jump to that cell.

...
Trace the cell's precedents.
credit: Image courtesy of Microsoft

Step

Click "Trace Precedents" to draw a line showing which cells have an effect on the selected cell. Tracing a cell's precedents helps identify why a cell's reference is circular in cases where the problem isn't immediately apparent.

...
Recheck for circular references.
credit: Image courtesy of Microsoft

Step

Fix the formula by removing the circular reference. Afterwards, check the Circular References list again to make sure no other errors appear. The status bar also displays a circular reference warning if one or more exist in the workbook.