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.
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.
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.
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.
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.
Tips & Warnings
- Press both "Trace Precedents" and "Trace Dependents" to draw two arrows, helping to resolve indirect circular references. These errors occur when two or more cells refer to each other in a loop.
- If you want to allow Excel to calculate a circular reference, open the program options from the "File" menu, switch to the "Formulas" tab and check "Enable Iterative Calculation." Because calculating a circular reference would normally lead to an infinite loop, Excel limits the calculation to a set number of iterations, which you can also change in the options. To speed the process, adjust the "Maximum Change" setting, which stops early if the result changes by a small amount between two iterations.
- Click "Show Formulas" or press "Ctrl-`" to display all cell formulas directly on the worksheet, making it easier to notice circular references.
- With "Enable Iterative Calculation" turned on, Excel won't list circular references in the Error Checking drop-down, but you can still use "Trace Precedents," "Trace Dependents" and "Show Formulas" to help track them.