In desktop Excel for Windows or Mac: 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.
On this page8
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: with iterative calculation off (the default) it warns you, and after the warning the cell might display 0 or the last calculated value; with iterative calculation on, it keeps iterating until a maximum number of loops is reached. 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 on Windows (Excel ▸ Preferences ▸ Calculation on Mac), 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 on Windows (Excel ▸ Preferences ▸ Calculation on Mac), 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: with iterative calculation off (the default) it warns you, and after the warning the cell might display 0 or the last calculated value; with iterative calculation on, it keeps iterating until a maximum number of loops is reached. 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 outcome you expected 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 warns you, A5 may show 0 or its last calculated value, 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, cells in the chain can show 0 or a stale last-calculated value, and every aggregate formula that depends on them inherits that number. Because Excel shows a number rather than a visible error in the cell, the wrong figure can persist across many dependent sheets before anyone notices. Check the referenced cells, argument order and result type in your workbook. Start with the worked example before changing an input, then compare the changed result with the calculation you expected. An error value or a result of a different type is a reason to inspect the inputs and arguments.
In Google Sheets
Formulas ▸ Error Checking ▸ Circular References and the tracing tools are the desktop workflow in Excel for Windows and Mac. In Excel for the web the formula-auditing commands can be more limited; open the file in the desktop app to locate the loop. Iterative calculation is under File ▸ Options ▸ Formulas on Windows and Excel ▸ Preferences ▸ Calculation on Mac. Google Sheets support for this task has not been verified. Check the function, arguments and workflow in the target product before transferring the example. Matching formula notation does not establish identical behaviour. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. For “remove circular references in excel”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.
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 or an old value instead of an error for a circular reference?
When iterative calculation is off (the default), Excel cannot evaluate the circular loop: it shows a warning, and after you close it the cell might display either 0 or the last calculated value, while the status bar names the circular reference. 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.