Excel’s circular reference warning means a formula depends on its own result, either directly or through other cells. To find the loop in Microsoft Excel for Microsoft 365 on Windows, open Formulas > Error Checking > Circular References, select a listed cell, and inspect the formula and its dependencies; then change the formula or, if the loop is intentional, enable iterative calculation deliberately.
What a circular reference means
A normal formula takes its inputs from other cells and returns a result to its own cell. A circular reference breaks that one-way pattern: the formula’s result becomes one of the inputs needed to calculate that same result. Excel cannot calculate a single answer from the dependency chain in the usual way, so it warns you.
The simplest example is a formula in A1 that adds 1 to A1 itself. With the formula entered in A1, the cell is both the calculation’s input and output, so the reference loops immediately. A less obvious loop can involve several cells: A1 depends on B1, B1 depends on C1, and C1 depends on A1. No individual formula needs to refer to itself directly for the chain to be circular.
A circular reference is not automatically evidence that the whole workbook is damaged. It may be an accidental mistake, such as including a total cell in its own sum, or it may be an intentional calculation that requires repeated approximation. The right fix depends on which of those situations applies.
How Excel reports the warning
When you enter or edit a formula that creates a circular reference, Excel may display a warning dialog. Depending on the workbook and calculation settings, it may also show a circular reference indicator in the status bar. A formula can remain in the cell after the warning, but its displayed result may be zero, incomplete, or otherwise not the intended value. Do not assume that a displayed number is correct simply because the cell contains a result.
On Windows, use Formulas > Error Checking > Circular References to see the circular-reference entries Excel has identified. Choose an address in the menu to go to that cell. If the menu lists more than one address, inspect them one at a time; correcting one loop can change what Excel identifies next. A listed address is a starting point, not necessarily the only cell involved in the loop.
The warning can be easy to miss in a large workbook, especially if you are not the person who created it. If you see “Circular References” on the status bar or suspect that a calculation is not updating as expected, check the menu rather than relying on the warning dialog alone.
Find the full loop, not just the first cell
Once you have selected a listed cell, read its formula and identify every referenced cell. Then follow those references into their formulas until you find the path that leads back to the starting cell. The loop may cross worksheets, so check sheet names in formula references and follow them where needed.
Excel’s formula-auditing tools can make this investigation easier. Select a formula cell and choose Formulas > Formula Auditing > Trace Precedents to show cells that feed into it. Trace Dependents shows cells that use its result. You can use the arrows to follow relationships across a worksheet; select Remove Arrows when you are done. These arrows help map dependencies, but you still need to inspect formulas to understand the actual calculation.
For complicated models, temporarily write down the cell addresses in order as you follow the chain. For example, a cycle might be A1 → B1 → C1 → A1. That record makes it easier to see which formula should change and helps avoid altering a cell that is part of the model for a different reason. Also inspect defined names if the visible formula uses a name rather than a cell address: a name can refer to a range or formula that leads back to the original cell.
Common accidental causes and how to fix them
One frequent cause is a total formula whose range includes the total cell itself. Suppose a worksheet has values in C2:C9 and the total belongs in C10. If the total formula includes C10, Excel sees the total as one of its own inputs. With the values in C2:C9 and the total in C10, the correct formula is =SUM(C2:C9). It adds the eight input cells and excludes the total cell.
Other common causes include:
- Entering a formula in the wrong row or column: A copied formula may shift its references into the cell where the result is being calculated.
- Copying a formula into its own input range: A formula that worked in one location can become circular after being filled across or down.
- Cross-sheet dependencies: A formula on one worksheet can depend on a second worksheet that, directly or through other cells, depends back on the first.
- Editing a model’s assumptions or ranges: A change to a formula, named range, or copied block can introduce a loop that was not present before.
For a running total, keep the previous total separate from the current row’s input. For example, if D1 contains a starting value of 0, each transaction amount is in column C, and the running total starts in D2, enter the formula below in D2 and fill it down. In this layout, D2 adds the starting value to the first amount in C2, so if C2 is 25 the result is 25. The next row can build on D2 without referring to itself.
=D1+C2
After changing a formula, check the result against a small manual calculation or known total. If the warning remains, revisit the Circular References menu: another loop may still exist, or the changed formula may have created a different dependency chain.
Intentional circular calculations and iteration
Some financial, engineering, or operational models use circular calculations intentionally. In such a model, one value depends on another value that, in turn, depends on the first. The intended answer is found by repeatedly recalculating values until they settle within a chosen tolerance or until Excel reaches a limit. Excel calls this process iterative calculation.
On Windows, the setting is in File > Options > Formulas. Under Calculation options, select Enable iterative calculation. The settings include a maximum number of iterations and a maximum change. The iteration limit controls how many calculation passes Excel can make; the change threshold controls how small the difference between passes must be before Excel stops. The appropriate settings depend on the model, and enabling iteration can affect circular formulas throughout the workbook.
Do not turn on iteration merely to make a warning disappear. First confirm that the cycle is part of the intended model and understand what each formula represents. If the formulas form an accidental loop, iteration can produce a number without correcting the underlying mistake. Even an intentional iterative model should be checked for sensible assumptions, convergence, and results that agree with an independent calculation where possible.
Excel for Mac has its own application settings interface, so Windows’ File > Options path does not apply there. Look under Excel’s calculation settings for the iterative calculation option; labels and locations can vary by Mac release. Excel for the web also has a different interface and does not provide the same Windows Options path. If you need to change workbook calculation settings and cannot find the control in your version, open the workbook in the desktop Excel app.
Calculation mode and other misleading symptoms
A circular reference is different from a workbook set to manual calculation. With manual calculation, formulas may not update immediately when inputs change, which can look like a broken or circular formula. On Windows, check Formulas > Calculation Options to see whether the workbook is set to Automatic, Automatic Except for Data Tables, or Manual. Choosing Automatic can recalculate formulas, but it does not repair a circular reference.
Also distinguish a circular reference from an ordinary formula error. Errors such as #VALUE! or #REF! have other causes; the circular-reference menu is a more direct clue that a dependency loop exists. If the workbook appears to calculate but the result seems wrong, verify the formulas and inputs rather than treating the warning as a display-only issue.
In shared or inherited workbooks, a circular reference may be intentional and documented elsewhere, or it may have been introduced during a recent edit. Before removing a loop in a business-critical model, ask the workbook owner what the formulas are meant to calculate. Keep a copy before making substantial changes so you can compare the original logic with the repaired version.
Prevent circular references in future work
Good worksheet structure makes loops less likely and easier to spot. Keep source inputs, intermediate calculations, and final outputs in distinct areas where practical. Label totals clearly, avoid placing a total inside the range it summarizes, and review formulas after filling or copying them across a table.
- Use a small test range before extending a new formula across a large model.
- Check the first and last formulas in a filled range, since relative references can shift at the edges.
- Use descriptive labels and document intentional iterative calculations, including the reason for the loop and the calculation settings used.
- After major edits, check Formulas > Error Checking > Circular References and review any listed cells.
These checks are especially useful in models with multiple worksheets or formulas copied from other workbooks. A clear layout does not guarantee that a loop cannot occur, but it makes the dependency chain easier for another person to audit.
Frequently Asked Questions
How do I find a circular reference in Excel?
In Excel for Microsoft 365 on Windows, choose Formulas > Error Checking > Circular References and select an address in the list. Inspect that formula and follow its cell references until you find the path back to the starting cell. The listed cell may be only one part of the loop.
Can I ignore the circular reference warning?
Only if you have confirmed that the circular calculation is intentional and configured iteration for the model. If it is accidental, ignoring the warning can leave a result that is incomplete or misleading. A warning should prompt you to investigate, not be treated as proof that the calculation is safe.
Why does Excel show a circular reference when I use SUM?
The SUM range may include the cell containing the formula. If the data is in C2:C9 and the total is in C10, summing C2:C10 includes the total cell and creates a loop. Exclude the formula cell from the range, and check for similar self-inclusion in other totals.
Will enabling iterative calculation fix every circular reference?
No. It allows Excel to recalculate intentional loops repeatedly, but it does not determine whether the formulas are logically correct. An accidental circular formula can still return an unintended value after iteration is enabled. Confirm the model’s purpose and review its outputs before relying on the result.
Why can’t I find the Circular References command?
Make sure you are using the desktop Excel app and looking on the Formulas tab under Error Checking. Excel for Mac uses a different settings interface, and Excel for the web does not use the Windows ribbon and Options paths in the same way. If the menu is unavailable in your version, open the workbook in desktop Excel to investigate its calculation settings and formulas.
Related
- Excel PivotTable Dates: How to Ungroup Them Easily
- Excel Power Query Automatic Refresh: What to Set Up
- Excel Phone List Template: Create a Contact List
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- DDE Error in Excel: What It Means and How to Fix ItA DDE error in Excel usually means Windows or another program cannot pass a request to Excel through…
- Excel Error 0xc000012f: Causes and FixesExcel error 0xc000012f is a Windows “Bad Image” error, not a worksheet or formula error. It means Windows…
- Excel Row Numbers Blue? Why It Happens and How to FixBlue row numbers in Excel usually mean that a filter is active on the worksheet, so some rows…
- VLOOKUP Not Working With Leading Zeros? Here’s WhyVLOOKUP often fails with leading zeros because Excel may store one lookup key as text, such as “00123,”…
- VLOOKUP Not Working in Excel? Show the Formula FixIf VLOOKUP appears in a cell instead of returning an answer, first turn off Formulas > Show Formulas…
- Microsoft Outlook Not Working Today? Check Status & FixesMicrosoft Outlook may be down today, but I can’t verify live service status from here; check Microsoft’s service-health…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.