How to Fix Circular Reference Error in Excel

A circular reference happens when a formula refers back to its own cell — directly or through a chain. Excel can't calculate it because it creates an infinite loop. Here's how to find and fix it.

What Does It Look Like?

When you create a circular reference, Excel shows a warning: "There are one or more circular references... this might cause them to calculate incorrectly."

The cell either shows 0 or keeps its old value instead of calculating.

Common Examples

Example 1: Direct Self-Reference

Cell C5: =SUM(C2:C5)   — C5 includes itself in the SUM range

Fix: Change to =SUM(C2:C4) — exclude the cell containing the formula.

Example 2: Running Total Mistake

Cell D2: =C2
Cell D3: =D2+C3
Cell D4: =D3+C4    — this is fine so far
Cell D5: =D4+C5+D5  — oops, D5 references itself

Fix: Remove the self-reference: =D4+C5

Example 3: Indirect Chain

Cell A1: =B1*2
Cell B1: =C1+10
Cell C1: =A1/5     — A1→B1→C1→A1 creates a loop

Fix: Break one link in the chain. Replace one of the three formulas with a direct value or a reference that doesn't loop back.

How to Find Circular References

1. Go to Formulas tab
2. Click Error Checking dropdown arrow
3. Hover over Circular References
4. Excel shows a list of all cells with circular references
5. Click any cell to jump directly to it

Shortcut: Look at the bottom status bar — Excel shows "Circular References: D5" when one exists.

Use Trace Precedents to Follow the Loop

1. Select the cell with the circular reference
2. Go to Formulas → Trace Precedents
3. Blue arrows show which cells feed into your formula
4. Follow the arrows — they'll loop back to the starting cell
5. Fix the formula that creates the loop
6. Click Remove Arrows when done

When You Actually Want a Circular Reference

Sometimes circular references are intentional — for example, iterative calculations like goal-seek scenarios or running counters.

To enable this:

1. File → Options → Formulas
2. Check Enable iterative calculation
3. Set Maximum Iterations (default: 100)
4. Set Maximum Change (default: 0.001)

Warning: Only enable this if you know exactly what you're doing. In 95% of cases, a circular reference is a mistake, not a feature.

Prevention Tips

• Never include the formula cell in its own SUM range — =SUM(A1:A10) in cell A10 is circular
• Use structured table references — =SUM(Table1[Amount]) automatically excludes the total row
• Check before copying formulas — dragging a formula down can accidentally create a self-reference in the last row
• Keep running totals simple — each cell should only reference the cell above it, not itself

Circular Reference vs #REF! Error

These are different problems:

• Circular reference = formula refers to itself (loop). Cell shows 0 or old value.
• #REF! error = formula points to a cell that was deleted. Cell shows #REF!

If you see #REF!, check our guide to fixing #REF! errors instead.