A circular reference happens when a formula refers back to its own cell, directly or through a chain of other cells. Excel can't resolve it — the calculation would loop forever — so it warns you. Here's how to find and fix circular references.
What a circular reference is
A circular reference is a formula that depends on its own result. The simplest case: cell A1 contains =A1+1. To calculate A1, Excel needs A1, which needs A1... an infinite loop. Excel detects this and shows a warning, usually displaying 0 in the cell and a message in the status bar.
Circular references can also be indirect: A1 refers to B1, B1 refers to C1, and C1 refers back to A1. The loop is longer but the problem is the same.
The warning
When you create one, Excel pops up a "Circular Reference Warning" dialog. If you dismiss it, the words "Circular References" appear in the status bar at the bottom of the window, often with a cell address. That address is your starting point for finding the loop.
How to find the circular reference
Excel has a built-in tool:
- Go to the Formulas tab.
- Click the arrow next to Error Checking (in the Formula Auditing group).
- Hover over Circular References.
- It lists the cells involved in the circular reference. Click one to jump to it.
This directly identifies the problem cells, saving you from hunting manually.
How to fix it
Trace the logic. Once you've found the cell, look at what it references, and follow the chain until you find where it loops back on itself. The Formulas > Trace Precedents tool draws arrows showing what a cell depends on, which helps you see the loop visually.
Break the loop. The fix is to rewrite the formula so it no longer depends on its own result. Usually this means:
- Referencing a different cell that holds the input you actually need.
- Restructuring so the calculation flows in one direction instead of circling back.
- Using a helper cell to hold an intermediate value, breaking the dependency.
Common accidental cause: including the formula's own cell in a SUM range. If C10 contains =SUM(C1:C10), it includes itself — a circular reference. Change it to =SUM(C1:C9) to exclude the total cell.
When circular references are intentional
Rarely, you genuinely want iterative calculation (some engineering and financial models rely on it). Excel can allow this: File > Options > Formulas > enable "Enable iterative calculation," and set the iteration limit. But leave this off unless you specifically need it — with it enabled, Excel stops warning you about circular references, so accidental ones silently produce wrong results instead of alerting you.
The takeaway
A circular reference means a formula depends on itself, creating a loop Excel can't resolve. Use Formulas > Error Checking > Circular References to find the culprit, then rewrite the formula to remove the self-dependency — often just excluding the total cell from its own SUM range. Keep iterative calculation turned off unless you truly need it, so Excel keeps catching these for you.