An Excel circular reference error means a formula refers, directly or indirectly, to its own cell. In Microsoft 365 for Windows, look under Formulas > Error Checking > Circular References, then edit the formula to break the loop; enable iterative calculation only when the loop is intentional.
What a circular reference means
A formula normally takes information from other cells and returns a result in its own cell. A circular reference occurs when that chain eventually leads back to the formula’s starting cell. The loop can be direct, involving one formula, or indirect, passing through several cells or worksheets.
For example, with a formula entered in A1, =A1+1 asks Excel to calculate A1 using the current value of A1. Excel cannot calculate that result in the usual one-pass way. A less obvious loop might be A1 referring to B1 while B1 refers back to A1. Long chains can make the cause hard to spot because the formula that triggers the warning may not be the cell where the mistake began.
Excel warns about circular references because they can make results depend on calculation settings or on previous calculations rather than on a clear, stable sequence of inputs. A displayed number is not proof that the formula is correct: depending on the workbook and calculation settings, Excel may show a warning and a value that is not the intended result.
How to find the circular reference
Start with Excel’s built-in list. On Windows, select Formulas > Error Checking > Circular References. If Excel identifies one or more cells, select a listed address to move to it. If the menu is unavailable or does not show a clear address, check the status bar at the bottom of the Excel window for a circular-reference notice, then inspect the relevant formulas.
For a multi-cell loop, follow the cell references one by one. On the Formulas tab, Trace Precedents draws arrows to cells used by the selected formula; Trace Dependents shows formulas that rely on the selected cell. These arrows can help expose a chain that turns back on itself. Use Formulas > Remove Arrows when you are done. You can also select a suspected cell and read its formula in the formula bar.
- Check the formula cell and the cells it refers to, including cells on other worksheets.
- Look for ranges that accidentally include the formula cell itself.
- Check whether a recently copied formula shifted its references into an unintended range.
- If the workbook has several circular references, correct one loop at a time and check the warning again.
Excel’s circular-reference list is useful, but it may not explain the whole dependency chain in a complex workbook. A formula can lead through other formulas before returning to the original cell. Tracing precedents and examining the formulas along that route is often the quickest way to understand the full loop.
Common causes in everyday worksheets
A frequent cause is including a total cell in the range being totaled. Suppose a worksheet lists figures in A1:A10 and puts the total in A10. With that layout, =SUM(A1:A10) entered in A10 includes A10 in its own calculation. If the figures belong in A1:A9 and the total belongs in A10, the corrected formula is =SUM(A1:A9), which totals only the nine input cells.
Other causes are less obvious. A copied formula may move a relative reference into the formula’s own row or column. A formula may refer to a helper cell that, through another formula, refers back to the original. A worksheet-wide total can also become part of a range used to calculate one of the values included in that total.
Manual calculation and old values can make a workbook harder to diagnose, but changing calculation mode does not remove a circular reference. The dependency still exists; calculation settings only affect when formulas recalculate and, if iteration is enabled, how Excel handles a loop. Avoid treating a displayed result as a fix until you have checked the references.
How to fix an accidental loop
The right repair is to decide which cells are inputs and which are outputs, then make the formulas follow that direction. In the total example, the input figures belong in B2:B4 and the total belongs in B5. With that layout, =SUM(B2:B4) entered in B5 returns 25 when B2 is 12, B3 is 8, and B4 is 5. The total cell is outside the input range, so there is no loop.
- Identify the formula Excel flags and write down the cells it references.
- Follow those references until you find the one that leads back to the starting cell.
- Correct the range, cell address, or worksheet reference so inputs do not depend on the output they help calculate.
- Recheck the result against a simple hand calculation or known test values.
If the circular reference was caused by an accidental range, correct the range rather than suppressing the warning. If a formula’s logic is unclear, consider adding a separate input or helper cell so each calculation has a clear direction. Before making broad changes in an important workbook, save a copy so you can compare results or restore the original.
When iterative calculation is appropriate
Some specialized models intentionally calculate a value by repeatedly using a result from an earlier pass. In that situation, a circular reference may be part of the design, and Excel’s iterative calculation setting can allow the workbook to calculate it. This is not a general-purpose way to silence an error: a mistaken loop can produce plausible-looking but incorrect numbers once iteration is allowed.
On Windows, open File > Options > Formulas and select Enable iterative calculation. The settings include Maximum Iterations, which limits the number of calculation passes, and Maximum Change, which sets a stopping threshold based on how much a result changes. More iterations do not automatically make a model correct. The workbook’s author should document why the loop is needed and choose settings that suit the model.
On Mac, the calculation controls are in Excel > Preferences > Calculation; labels and organization can vary by release. Excel for the web does not offer the same full set of desktop calculation controls, so use the desktop app when you need to change iterative-calculation settings. If the workbook is shared, make sure other users understand that the results depend on iteration being enabled and on the workbook’s settings.
Check the model before accepting a result
When iteration is intentional, test the workbook with inputs whose expected behavior is known. Check whether the results settle as expected when the iteration limit or change threshold is adjusted, and look for outputs that shift substantially with small setting changes. A model that is sensitive to these choices may need review before anyone relies on its results.
Also check whether formulas contain volatile functions or depend on changing external data. Those factors can cause recalculation to happen more often and make iterative models harder to interpret. Keep a note in the workbook describing the circular logic, the relevant settings, and which cells are inputs. That context is valuable when someone opens the file on another computer or after a later edit.
Preventing circular references in future work
Good worksheet structure reduces accidental loops. Keep raw inputs, intermediate calculations, and final outputs in separate areas or columns. Place totals outside the ranges they summarize, and use clear labels so a formula’s role is apparent. When copying formulas, inspect the first and last cells of the copied range rather than assuming every reference shifted as intended.
For shared workbooks, add a brief note beside any intentional iterative model and avoid enabling iteration globally just to make one workbook warning disappear. Excel’s calculation settings may affect other open workbooks, so a change can have consequences beyond the sheet currently on screen. If a file suddenly begins showing a circular-reference warning after an edit, undoing or reviewing the latest formula changes can be a useful first check.
Frequently Asked Questions
How do I find a circular reference in Excel?
On Windows, use Formulas > Error Checking > Circular References and select a listed cell. Then inspect its formula and trace precedents to find the rest of the dependency chain. Check the status bar as well if the menu does not make the location clear.
Why does Excel show a circular reference when my formula looks correct?
The formula may refer to a range that contains its own cell, or one of its referenced cells may lead back to it through another formula. The loop can involve different rows, columns, or worksheets, so reading only the original formula may not reveal the cause.
Should I turn on iterative calculation to remove the warning?
Only if the circular dependency is intentional and the workbook is designed to calculate iteratively. For an accidental loop, correct the references instead. Iteration allows Excel to calculate a loop; it does not establish that the model’s logic or results are valid.
Can a circular reference return a number?
Yes, Excel may display a number in some circumstances, especially when iterative calculation is enabled or the workbook retains a prior value. A visible result alone does not confirm that the formula is correct. Check the warning, the dependency chain, and the calculation settings.
Does a circular reference always mean the workbook is broken?
No. Some specialized models deliberately use iterative calculation, but the design should be understood and documented. In ordinary totals, budgets, and summary formulas, a circular reference is more often an accidental range or reference error that should be corrected.
Related
- Excel Data Validation List Based on a Cell: How It Works
- Excel Data Validation List: Keep Dropdowns in A-to-Z Order
- Excel Data Validation List Auto Update: How to Set It Up
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Unable to Remove a Page Break? Find the Right FixIf Excel will not remove a page break, first check whether it is manual or automatic: select the…
- Excel #DIV/0! Error: How to Hide It Without Hiding DataTo stop Excel showing a #DIV/0! error, wrap a division formula in IFERROR or check that its denominator…
- Excel Cell Keeps Changing to Date? Stop the Auto-FormatIf an Excel cell keeps changing to a date, format the cell as Text before entering the value,…
- Excel Undo and Redo Not Working? Causes and Fixes GuideIf Undo or Redo is unavailable in Excel, first check the Undo and Redo arrows on the Quick…
- Excel Freeze Panes Not Working on Selection? Fix ItIf Excel Freeze Panes is not working on your selection, first select the cell immediately below the rows…
- Excel Unique Name Error: Why It Appears and How to Fix ItIf Excel says a name is already taken or must be unique, the name you are trying to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.