Guides
How to Find and Fix Circular References in Excel
Updated 2026-10-18
You open a file and Excel says there are one or more circular references that may cause formulas to calculate incorrectly. You click OK, and a total shows 0, or a number no longer matches last month's.
What a circular reference is
A formula refers, directly or indirectly, to its own cell:
- directly:
A10 contains =SUM(A1:A10), so the sum includes itself;
- indirectly:
B1 refers to C1 and C1 refers back to B1, possibly across several sheets.
The usual causes are a sum range dragged one row too far and copied formulas whose references shifted until they point back at themselves.
Both outcomes are unreliable
- With iterative calculation off, Excel warns you, and formulas on the loop usually show 0 or their previous result.
- With iterative calculation on (File → Options → Formulas → Enable iterative calculation) there is no warning at all; Excel recalculates the loop a set number of times and settles on a number that can change with the iteration count or calculation order, so another computer or another setting gives another result.
Unless the model is deliberately iterative (some finance and engineering models are), turn iterative calculation off; otherwise it only hides circular references.
Method 1: find them one by one with Error Checking
- Formulas → Error Checking drop-down arrow → Circular References lists a cell on a loop; click it to jump there.
- The status bar at the bottom also shows "Circular References: Sheet1!A10".
- With the cell selected, use Formulas → Trace Precedents to follow the arrows to the step that loops back, and fix the formula.
- Once fixed, the next one appears; repeat until the list is empty.
The catch: it shows one cell at a time, so long loops or loops across several sheets take many rounds.
Method 2: find them all and break them in place
xlszip's check-up scans the formula dependencies of the whole workbook:
- the report says how many circular references there are, with an example of the cells a loop passes through (e.g.
Summary!B3 → Detail!C9: B3 uses C9, and C9 leads back to B3);
- it also tells you whether iterative calculation is on;
- with the fix option ticked you can break them in one click right on the page: cells on each loop are cleared and filled red, the original formula goes into a comment on the cell so you can correct and re-enter it, iterative calculation is turned off, and Excel recalculates the whole workbook on open.
The check-up is read-only and deletes your file once the report is ready; a new file is produced only when you choose to fix. Dependencies are found by static scanning: references built at run time with INDIRECT or OFFSET aren't visible, so it may over-report but won't miss a loop.
If the file is also damaged or won't open, the repair tool breaks circular references while repairing.
Typical fixes
- Sum range too long: change
=SUM(A1:A10) to =SUM(A1:A9).
- A total that sums the whole column it sits in:
=SUM(A:A) in column A always loops; use an explicit range or put the total in another column.
- Two cells referring to each other: decide which one is the input and type a value there.