How to find and fix circular references in Excel

What a circular reference actually means

A circular reference is a formula that depends on its own result — either directly (=A1+1 sitting in cell A1) or through a chain (A1 reads B1, B1 reads C1, C1 reads A1 again). Excel can't settle on an answer by normal calculation, so it warns you once, shows Circular References in the status bar, and typically displays 0 in the affected cells. Those zeros then flow into everything downstream, which is why a single loop can quietly flatten totals across a whole model.

The most common causes

• A total that includes itself — =SUM(A1:A10) typed into A10, so the sum range swallows the sum.
• A loop across sheets — a summary tab reads a detail tab that reads the summary back; no single formula looks wrong.
• A pasted block that lands on its own inputs, splicing a dependency back into itself.
• An intentionally circular model — classic in finance: interest expense depends on the debt balance, which depends on cash, which depends on interest expense. The loop is the design, not a mistake.

Step-by-step fix

1. Locate the loop. Go to the Formulas tab → Error Checking dropdown → Circular References. Excel lists a cell involved in a loop — click it to jump there. It shows one loop at a time, so after each fix, check the menu again until it's empty. The status-bar indicator only names a cell when the loop is on the active sheet, so trust the menu, not the status bar.
2. Follow the chain. With the cell selected, Formulas → Trace Precedents draws arrows to what it reads; keep tracing until you see the path back to where you started.
3. Break it. Usually that means moving a total out of the range it sums, or replacing one link in the chain with a hard-coded input or a prior-period reference so the dependency runs one way.
4. Recalculate (F9) and confirm the status bar no longer shows Circular References.

The iterative-calculation nuance

Some circular models are deliberate. Excel will compute them only if iterative calculation is switched on (File → Options → Formulas → Enable iterative calculation), which makes Excel loop until values converge. That works — but the setting lives on the machine and in the session, not reliably in the file. Open the same workbook on a colleague's default-configured Excel and the model shows zeros or a circular-reference warning instead of results. If your model is intentionally circular, treat that as a portability risk: document it prominently, or restructure the loop algebraically so it computes anywhere.

Or let us map every loop in seconds

Upload your workbook and we'll trace every circular chain — including cross-sheet loops Excel's menu makes you hunt one cell at a time — and tell you whether the file depends on iterative calculation to produce numbers at all.

More fixes: run a full spreadsheet audit · fix the #REF! error · fix the #DIV/0! error