Circular Reference in Excel: Find It and Fix It
A circular reference is a formula that refers to its own cell. How to find one in Excel, the usual causes like totals inside their range, and fixes.
By Ayoub Farid · · 1 min read
Excel warns "There are one or more circular references" and your total shows 0. A circular reference means a formula depends on its own result, directly or through other cells.
The classic cause
Total in B14:
=SUM(B2:B14)The range includes B14 itself. Fix: =SUM(B2:B13).
Same with whole columns: =SUM(B:B) placed anywhere in column B.
Indirect circles
- C2:
=B2*D2 - D2:
=C2/10
C2 needs D2, which needs C2. No single formula looks wrong, but together they loop.
How to find it
- Formulas > Error Checking > Circular References lists the cells involved.
- The status bar at the bottom shows "Circular References: B14".
- Use Trace Precedents to see the arrows.
How to fix it
- Tighten the range so it doesn't include the formula's own cell.
- Move the total outside the data column (e.g. a summary area).
- Break indirect loops by calculating from inputs, not from results.
Intentional circular references
Some financial models (interest that depends on a balance that depends on interest) use iterative calculation (File > Options > Formulas > Enable iterative calculation). Avoid this unless you know exactly why you need it: it can hide real mistakes.
Frequently asked questions
What is a circular reference in Excel?
A formula that refers to its own cell, directly or through a chain of other formulas.
How do I find a circular reference?
Formulas > Error Checking > Circular References, or look at the status bar.
Why does my SUM show 0 with a circular reference warning?
The total cell is included in its own range. Shorten the range.
Should I enable iterative calculation?
Only for deliberate models that need it. For normal sheets, fix the formula instead.