What's Happening
A circular reference means a formula's result depends on itself — either directly, or through a chain of other cells. Excel can't resolve the loop. It either shows 0 or the last computed value. Neither is correct.
The exact warning you'll see:
There are one or more circular references where a formula refers to its own cell either directly or indirectly
Here's where this typically goes wrong:
- You typed a formula directly into the cell it references — for example,
=A1+B1sitting inside cellA1 - A SUM range quietly swallows the cell holding the SUM formula itself (classic:
=SUM(B1:B10)placed inB10) - Cell A references B, B references C, and C loops back to A — no single formula looks wrong, but the chain is circular
- You pasted a formula into a cell that falls inside its own reference range
Step 1: Find the Circular Reference Cells
Check the status bar at the very bottom of Excel first. If a circular reference exists, you'll see something like "Circular References: B10". That's the cell address causing the problem.
Don't see it? Navigate to:
Formulas → Formula Auditing → Error Checking → Circular References
The dropdown lists every offending cell. Click one to jump straight to it.
Tracing the Loop with Auditing Arrows
Once you're on the problem cell, visualize the reference chain:
- Formulas → Trace Precedents — draws arrows to every cell feeding into this one
- Formulas → Trace Dependents — draws arrows to every cell that reads this one
Blue arrows are normal. When you spot an arrow looping back to its own starting cell, that's your circular path.
Step 2: Fix the Formula
Case 1 — Direct Self-Reference
The most common mistake: a SUM or AVERAGE range that accidentally includes the formula cell itself.
❌ Cell B10 contains: =SUM(B1:B10) ← B10 is inside its own range
✅ Fix: =SUM(B1:B9) ← exclude the formula cell
Case 2 — Indirect Circular Loop
Trickier to track down. The loop spans multiple cells, so no single formula looks wrong on its own. Example:
Cell A1: =C1 * 2
Cell B1: =A1 + 5
Cell C1: =B1 - 3 ← C1 depends on B1 → A1 → C1. Full circle.
Break the chain by converting one cell to a hardcoded input value:
Cell A1: =C1 * 2
Cell B1: =A1 + 5
Cell C1: 10 ← static input, no formula
Case 3 — Paste Gone Wrong
You copied a formula and landed it inside its own reference range. Two ways to recover:
❌ =AVERAGE(D1:D20) pasted into cell D15
✅ Option A: Move the formula outside the range — paste it into D21 instead
✅ Option B: Adjust the range to skip the formula cell: =AVERAGE(D1:D14,D16:D20)
Step 3 (Optional) — Enable Iterative Calculation
Most circular references are accidents. A few formulas, though, are intentionally circular — compound interest that references its own previous result is a classic example. For those cases, turn on iterative calculation:
File → Options → Formulas → Enable iterative calculation
- Maximum Iterations: how many recalculation passes Excel runs (default: 100)
- Maximum Change: Excel stops when the difference between passes drops below this threshold (default: 0.001)
One caution: enabling this for a normal spreadsheet just hides the bug. Fix the formula — don't mask it.
Verify the Fix
- Press Enter after editing the formula.
- Look at the status bar — "Circular References" should have disappeared.
- Check Formulas → Error Checking → Circular References — the submenu should be grayed out (empty).
- Confirm the cell now shows a real calculated value instead of
0. - Hit F9 to force a full recalculation and verify nothing else breaks.
Lessons Learned
- SUM and AVERAGE at the bottom of a column are the most frequent culprits — always check that the range stops one row above the formula cell.
- Use Ctrl+` (grave accent) to toggle formula view while building complex spreadsheets. Seeing every formula at once makes accidental self-references obvious immediately.
- Circular reference appeared right after a paste? Hit Ctrl+Z and paste to a cell outside the source range.
- Keep iterative calculation off by default — it silences errors that Excel would otherwise catch for you.