In Excel: a formula refers directly or indirectly to its own cell, creating a loop Excel cannot resolve — locate it via Formulas ▸ Error Checking ▸ Circular References and break the loop by restructuring the formula.
Fix:Circular Reference
What causes Circular Reference
A circular reference occurs when a formula in cell X includes cell X in its own calculation — either directly (=A1+A1 entered in A1) or indirectly through a chain (A1 refers to B1 which refers to C1 which refers back to A1). Excel cannot compute an infinite loop, so it either shows 0 (when iterative calculation is off, the default) or it keeps iterating until a maximum number of loops is reached (when iterative calculation is on). The status bar at the bottom of Excel shows "Circular References: [cell address]" when one exists. Circular references are almost always unintentional — a formula accidentally reaching back to include its own cell is the most common cause.
How to fix Circular Reference
- Go to Formulas ▸ Error Checking ▸ Circular References — Excel lists the cell(s) involved in the circular reference chain.
- Click the cell address in the Circular References list to jump directly to it.
- Read the formula in that cell and identify which part references the cell itself (directly or via a chain through other cells).
- Move the formula to a different cell that is not in its own reference range — this is the most common fix.
- If the reference chain is indirect (A→B→C→A), use Formulas ▸ Trace Precedents on each cell in the chain to map the loop.
- If iterative calculation is intentional (e.g. for a running balance), enable it under File ▸ Options ▸ Formulas ▸ Enable Iterative Calculation, and set a Maximum Iterations and Maximum Change value — but document this clearly as it makes the model harder to audit.
Go to Formulas ▸ Error Checking ▸ Circular References — Excel lists the cell(s) involved in the circular reference chain.
Click the cell address in the Circular References list to jump directly to it.
Read the formula in that cell and identify which part references the cell itself (directly or via a chain through other cells).
Move the formula to a different cell that is not in its own reference range — this is the most common fix.
If the reference chain is indirect (A→B→C→A), use Formulas ▸ Trace Precedents on each cell in the chain to map the loop.
If iterative calculation is intentional (e.g. for a running balance), enable it under File ▸ Options ▸ Formulas ▸ Enable Iterative Calculation, and set a Maximum Iterations and Maximum Change value — but document this clearly as it makes the model harder to audit.
What this does
A circular reference occurs when a formula in cell X includes cell X in its own calculation — either directly (=A1+A1 entered in A1) or indirectly through a chain (A1 refers to B1 which refers to C1 which refers back to A1). Excel cannot compute an infinite loop, so it either shows 0 (when iterative calculation is off, the default) or it keeps iterating until a maximum number of loops is reached (when iterative calculation is on). The status bar at the bottom of Excel shows "Circular References: [cell address]" when one exists. Circular references are almost always unintentional — a formula accidentally reaching back to include its own cell is the most common cause. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “remove circular references in excel”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the tool above before you touch the real workbook. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. The point is a data step that keeps your analysis trustworthy, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
Scenario: you enter =SUM(A1:A10) in cell A5, which is inside the range A1:A10. The formula is trying to include its own result in the sum — an endless loop. Excel shows 0 in A5 and the status bar says "Circular References: A5." To fix: move the formula to a cell outside the range (e.g. A11) or change the range to A1:A4, A6:A10. A subtler case: =IF(B1="done", A1, A1+1) in A1 — A1 always refers to itself. Fix by restructuring: put the counter in a helper cell that A1 does not reference. Treat a circular reference warning as an urgent bug, not a cosmetic issue. In the default (non-iterative) mode, every cell in the chain returns 0, silently corrupting every aggregate formula that depends on those cells. Because Excel continues to show 0 rather than a visible error, the corruption can persist undetected across many dependent sheets before anyone notices the numbers are wrong. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.
In Google Sheets
If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. Treat “remove circular references in excel” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.
Common mistakes
- Accidentally selecting "the entire column" in a
SUMrange when theSUMformula itself is in that column —=SUM(A:A)entered in any cell in column A creates an immediate circular reference. - Enabling iterative calculation to "fix" a circular reference without understanding the chain — iterative calculation converges to a number, but that number may be meaningless if the loop is accidental rather than intentional.
- Copying a formula from one cell to a range that includes the source cell — Excel shifts relative references during the copy, which can accidentally create new circular references in the copied cells.
Frequently asked questions
How do I find a circular reference in a large workbook?
Go to Formulas ▸ Error Checking ▸ Circular References. Excel lists every cell that is part of a circular loop. If the list is empty but the status bar still says "Circular References," check every sheet — the circular reference may be on a different sheet than the one currently visible.
Why does Excel show 0 instead of an error for a circular reference?
When iterative calculation is off (the default), Excel cannot evaluate the circular loop so it returns 0 and displays a warning in the status bar. When iterative calculation is on, Excel evaluates the formula the specified number of times and displays the last computed value. Neither is "correct" — you should break the loop.
Are circular references ever intentional and correct?
Rarely — some financial models use them intentionally for running totals, interest-on-interest calculations, or target-seeking scenarios where iterative convergence is desired. In those cases, enable iterative calculation consciously and document the reason. For the vast majority of users, a circular reference is a mistake that should be resolved by restructuring the formula.