To resolve a circular reference in Excel, find the formula that refers to its own cell, directly or through other formulas, and change the formula or its input range so the loop ends. In Microsoft 365 for Windows, start with Formulas > Error Checking > Circular References, then inspect the listed cells and correct the source of the loop.
What a circular reference means
A formula has a circular reference when its calculation depends on its own result. The dependency can be direct: a formula in A3 that includes A3 in its calculation. Or it can be indirect: a formula in A3 depends on B3, while B3 depends on A3. Either way, Excel cannot calculate a normal one-way sequence because it would need a result before it can finish calculating that result.
This often happens by accident when a formula includes its own cell in a range, when a copied formula shifts to an unexpected location, or when a group of formulas depends on one another. Excel may show a circular-reference warning, and the workbook may display an unexpected value or fail to update as expected. A circular reference is not always a sign that the intended calculation is wrong: some financial and engineering models deliberately use a loop and rely on iterative calculation. But unless the workbook is designed for that, it is usually best to remove the loop.
Find the cell causing the loop
On Windows, open the Formulas tab and choose Error Checking > Circular References. Excel lists cells it has identified as part of a circular reference; select an entry to move to that cell. If the command is unavailable, check that the workbook is open in desktop Excel and that a circular reference has been detected. The status bar may also show a circular-reference notice and a cell address.
Use the formula bar to inspect the selected cell. Look for the cell itself in the formula, or for a range that includes it. If the selected formula does not obviously refer to itself, follow its precedents: inspect the cells it uses, then the cells those formulas use. Excel’s Formulas tab includes Trace Precedents and Trace Dependents tools that can help visualize those relationships.
For a direct example, suppose A1 contains 10 and A2 contains 20, and the formula is entered in A3. The following formula includes A3 in the range being added, so A3 depends on its own result:
With 10 in A1 and 20 in A2, and the formula entered in A3: =SUM(A1:A3)
Check more than the first listed cell if the loop is indirect. A workbook can contain several separate circular references, and correcting one may expose another. Also inspect formulas recently copied, filled, or edited, since a relative reference can shift a range into the formula’s own row or column.
Fix the formula or its range
The safest fix is usually to make the formula refer only to its actual inputs. In the example above, if A3 is meant to total the two values above it, exclude A3 from the range:
With 10 in A1 and 20 in A2, and the corrected formula entered in A3: =SUM(A1:A2)
The result is 30. This change keeps the intended calculation while removing the self-reference. In a real workbook, confirm what the formula is supposed to calculate before editing it; deleting a reference without understanding its role can remove a necessary input or change the meaning of the result.
Other common repairs include moving a formula to a separate output cell, correcting a range boundary, or separating inputs from calculated outputs. If a formula in D2 is supposed to calculate a commission from sales in B2 and a rate in C2, for example, place the calculation in D2 and keep B2 and C2 as inputs.
With sales in B2, a commission rate in C2, and the result in D2: =B2*C2
For sales of 500 and a rate of 5%, this returns 25. Do not put the result back into B2 or C2 if those cells are also being used as inputs; that would create a dependency loop. After a repair, check the displayed result against a hand calculation or a known example, and save the workbook once the corrected behavior is confirmed.
Check indirect references and copied formulas
A formula can be circular even when its own address does not appear in its formula text. For instance, A1 might depend on B1, and B1 might depend on A1. Each formula looks reasonable by itself, but together they form a loop. Follow the cell references until you return to the starting cell, or use formula-tracing arrows to see the chain.
With A1 containing =B1+1 and B1 containing =A1/2, each cell depends on the other. These formulas do not define a straightforward one-pass calculation. Decide which value should be an input and which should be calculated, then rewrite the formulas so the dependency flows in one direction.
Copied formulas deserve special attention. A formula copied down a column may shift its references as intended, but a range near the bottom of a table can accidentally include the result cell. If a reference is meant to stay fixed while copying, use an absolute reference such as $B$2; if the range should expand, verify that it does not include the output cell. Structured table references can also make a formula easier to read, but they do not prevent a circular dependency by themselves.
When iterative calculation is appropriate
Some models deliberately use circular references because a value is calculated by repeatedly updating related results until they settle close to a stable answer. In Excel, this behavior is controlled by iterative calculation. On Windows, go to File > Options > Formulas, select Enable iterative calculation, and set Maximum Iterations and Maximum Change. These settings affect calculation in the workbook, so do not enable them just to hide an unexplained warning.
Maximum Iterations limits how many calculation passes Excel makes. Maximum Change sets the amount by which values must change between passes before Excel considers the calculation close enough to stop. More iterations or a smaller change threshold can improve convergence in some models, but may increase calculation time. A loop may still fail to settle, oscillate between values, or stop at a result that is not meaningful; iterative calculation does not prove that the model is mathematically sound.
For example, a model might calculate interest on an average balance, while the closing balance includes that interest. With an opening balance of 1,000 in A2, a closing balance in B2, interest in C2, and a one-period rate of 1% in D2, the formula in B2 adds interest to the opening balance:
With opening balance in A2, interest in C2, and closing balance calculated in B2: =A2+C2
With opening balance in A2, closing balance in B2, and the rate in D2, the formula in C2 calculates interest on the average balance:
With the opening and closing balances in A2 and B2 and a period rate in D2: =AVERAGE(A2,B2)*D2
Those formulas intentionally depend on each other. Under iterative calculation, they converge to a closing balance of about 1,010.05 and interest of about 10.05, subject to the workbook’s iteration and change settings. The values differ slightly from a simple 1% of 1,000 because the example calculates interest on the average of the opening and closing balances. Document the reason for the loop and test the result against the model’s intended assumptions.
Common causes and prevention
- A total includes its own cell. Check the first and last cells in SUM ranges, especially when inserting rows or copying a formula.
- Inputs and outputs share a cell. Keep source data separate from calculated results so a formula does not rely on a value it is also trying to produce.
- Two formulas depend on each other. Map the calculation flow and identify which value should be entered independently.
- A copied formula moved references. Review relative and absolute references after filling formulas across or down.
- A deliberate model loop was not documented. Explain why iteration is enabled and record suitable calculation settings so another user can distinguish intended behavior from an error.
Good worksheet design reduces the chance of accidental loops. Put raw inputs in a clearly marked area, calculations in another area, and final outputs in a separate section. Use descriptive labels and avoid placing a total inside the range it totals. When several sheets are involved, include sheet names and cell addresses in your notes so someone reviewing the workbook can trace cross-sheet dependencies.
Troubleshoot after making a change
Once you edit a formula, check the Circular References menu again and confirm that the relevant entry has disappeared. Recalculate the workbook if necessary; Excel’s calculation mode can be set to Manual, in which case formulas may not update automatically. On Windows, check Formulas > Calculation Options and select Automatic if that is appropriate for the workbook’s workflow.
If the warning remains, trace the references further rather than repeatedly changing unrelated formulas. Check other worksheets, named ranges, and formulas that refer back to the affected cells. If the workbook uses iterative calculation intentionally, verify both the formulas and the iteration settings, then test a few inputs whose expected results can be calculated independently. On Mac, menu labels and locations can vary by Excel release; calculation preferences are available through Excel > Settings or Preferences > Calculation. Excel for the web has a different interface, so use desktop Excel when you need the Windows ribbon paths or full workbook calculation controls.
Frequently Asked Questions
Is a circular reference always an error?
No. It is usually accidental in ordinary worksheets, but some models deliberately use a circular dependency with iterative calculation enabled. If the loop is intentional, document why it exists and verify that the results converge correctly.
Why does Excel show a circular-reference warning?
Excel has detected a formula that depends on itself, either directly or through other formulas. Use Formulas > Error Checking > Circular References to locate a reported cell, then trace its inputs to find the complete loop.
How do I find a circular reference on another sheet?
Start with the cells listed under Circular References, then inspect the formulas and follow their precedents across worksheets. A loop may include cells on several sheets, so continue tracing until the references return to the starting cell.
Should I turn on iterative calculation to remove the warning?
Only if the workbook is designed to calculate a deliberate loop. Iterative calculation changes how Excel handles circular formulas; it does not repair an accidental self-reference or guarantee a correct answer.
Why does the warning remain after I fix a formula?
There may be another circular reference, or the remaining loop may involve a different cell or worksheet. Recheck the Circular References list, trace the dependencies, and make sure the workbook has recalculated.
Related
- Marked Scatter Plot in Excel: Create and Format One
- Matrices in Excel Solver: When It Helps and When It Doesn’t
- Lock a Slicer in Excel: Keep It in Place and Usable
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Quick Search in Excel: Find Data FastTo quickly search an Excel spreadsheet, press Ctrl+F in Excel for Microsoft 365 on Windows, type the word…
- Unhide Columns in Excel: Keyboard Shortcuts and StepsTo unhide columns in Excel for Microsoft 365 on Windows, select the column headers on both sides of…
- Excel Geographic Heat Map Template: How to Make OneYes. In Excel for Microsoft 365 on Windows, make a geographic heat map with a table containing a…
- Google Sheets Alphabetize Function: Sort Data A to ZGoogle Sheets does not have a function named ALPHABETIZE; use the SORT function to create an alphabetized result,…
- How to Copy a Sum From Excel’s Status Bar to a CellTo copy a selected range’s sum from Excel’s status bar, select the cells, make sure Sum is enabled…
- How to Wrap Text in Google Sheets Without Cutting It OffTo wrap text in Google Sheets, select the cell or cells, then choose Format > Wrapping > Wrap.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.