Excel Circular References: Find, Fix, and Understand Them

Errors & fixes · Updated November 21, 2026

A circular reference in Microsoft Excel happens when a formula refers to its own cell, either directly or through a chain of other formulas. To find one in Excel for Microsoft 365 on Windows, open Formulas > Error Checking > Circular References; then edit the listed formula or its dependencies to break the loop, unless the circular calculation is intentional.

What a circular reference means

Excel normally calculates a cell by using values from other cells. A circular reference creates a loop: a formula needs a value that, directly or indirectly, depends on that same formula. The calculation has no straightforward starting point, so Excel warns you rather than following a normal one-way sequence of calculations.

A direct circular reference is the simplest case. With a formula entered in A1, =A1+1 asks Excel to calculate A1 by first knowing the value of A1. There is no independent input to resolve it. With iterative calculation turned off, this example has no dependable calculated answer; Excel flags the circular reference and may display an initial or previous value, often zero.

An indirect circular reference involves multiple cells. For example, A1 might depend on B1 while B1 depends on A1. Longer loops can include cells on different worksheets, so the formula that appears to be wrong may not be the cell that closes the cycle. Circular references are not the same as a typo or a missing-cell error: the individual formulas can be syntactically valid while their dependency structure is impossible to calculate normally.

How Excel detects and calculates the loop

Excel tracks which cells depend on other cells so that it can recalculate formulas when inputs change. In a typical worksheet, dependencies form a sequence: source data feeds a calculation, which feeds a final result. A circular reference creates a cycle in that sequence. Excel can identify that a cycle exists, but it cannot determine a unique answer just by following the formulas once.

By default, iterative calculation is disabled in a new workbook setup. Excel warns about the circular reference and may stop recalculating normally until the loop is removed or iteration is enabled. Enabling iteration does not automatically make a formula correct. It tells Excel to recalculate a circular formula repeatedly, using the previous result as an input, until it reaches a stopping limit. Whether that process converges to a meaningful answer depends on the formulas and the data.

A warning can be easy to miss if a workbook already contains a circular reference or if a user dismisses the message. The status bar may show Circular References along with a cell address. A displayed number is not proof that the workbook is calculating correctly: it could be a starting value, a stale result, or an iteration result that depends on the calculation settings.

How to find a circular reference

In Excel for Microsoft 365 on Windows, use the ribbon path Formulas > Error Checking > Circular References. If Excel has identified a loop, the submenu lists a cell address. Select an address to jump to that cell and inspect its formula. If more than one item is listed, check each one; correcting one loop can expose another.

  • Read the formula in the listed cell and note every referenced cell or range.
  • Follow those references to see whether one eventually leads back to the original cell. Check other worksheets too.
  • Inspect the status bar for a circular-reference notice and address. Treat it as a clue, not necessarily a complete map of every loop.
  • Use Formulas > Trace Precedents or Formulas > Trace Dependents to help follow cell relationships. These arrows can help, but they may not make a complicated, multi-sheet cycle obvious at a glance.
  • After changing a formula, revisit the Circular References menu and check whether Excel still lists a cell.

The menu may identify only one point in a more complex loop, so inspect the referenced formulas rather than assuming that the first listed cell is the sole cause. If you cannot see formulas easily, the Windows shortcut Ctrl+` toggles formula display in the worksheet; press it again to return to calculated values. The key is the grave accent, often located near the top-left of the keyboard, and its position can vary by layout. On a Mac, the shortcut and key location can differ by keyboard, so use the Show Formulas command on the Formulas tab if the shortcut is inconvenient. Excel for the web may not expose every desktop diagnostic or calculation control; use the desktop app for a full investigation.

How to fix a circular reference

Most accidental loops are fixed by changing a formula so the calculation flows in one direction. Suppose A1:A10 contains the numbers 1 through 10, and you want a total beneath them. The formula belongs in A11, outside the range being added. With those values in A1:A10, enter =SUM(A1:A10) in A11; the result is 55. Putting that same formula in a cell inside A1:A10 would include the formula cell in its own total and create a circular reference.

If a formula in a total cell accidentally includes that total cell, narrow the referenced range or move the total outside the range. If one formula depends on a result that in turn depends on it, separate the calculation into input, intermediate, and output cells. A helper cell is often clearer and safer than a single complicated formula, especially when several people maintain the workbook.

For an indirect loop, trace the chain until you find the assumption that is wrong. For example, a tax or commission calculation might feed its result back into the amount used to calculate that same tax or commission. Decide which value should be an input and which should be an output, then restructure the formulas to reflect that business rule. Do not simply delete a formula or replace it with a fixed number unless that change is appropriate for the workbook.

Common causes and mistakes

A frequent cause is including a formula cell in its own SUM range. This often happens when someone selects a column or row broadly and places a total inside the selected range. Another common cause is a copied formula whose relative references shifted into a cell that depends on the result. Checking the formula bar and the referenced cells usually reveals this kind of mistake.

Loops can also arise through linked worksheets, named ranges, or formulas that refer to a summary cell which itself uses the original data. A workbook may have been designed by someone else, so do not assume that a circular reference is local to the currently visible sheet. Hidden sheets and named ranges can make a dependency chain less obvious. Reviewing the Name Manager and the formulas in linked sheets may help when the visible cell references do not explain the warning.

One misconception is that every circular reference is harmless if a number appears in the cell. The number may be a leftover result or may change when Excel recalculates. Another is that turning on iterative calculation is a universal fix. It can suppress the default problem by allowing repeated calculation, but a poorly designed loop can still produce an unstable, inaccurate, or misleading result.

When iterative calculation is appropriate

Iteration can be useful when the calculation is deliberately defined as a repeated approximation. For example, a model might use a result from one pass as an input to the next pass, and the values may settle toward a stable answer. This is different from an accidental self-reference: the workbook owner should know why the loop exists, what result is expected, and how much approximation is acceptable.

Consider a deliberately simple example in which A1 contains =(A1+10)/2. If iteration starts from zero, successive values approach 10: 5, 7.5, 8.75, and so on. After 14 passes, the mathematical value is about 9.99939. That example illustrates convergence, not a recommended general-purpose formula. The displayed result depends on the workbook’s iteration settings and starting value, and the formula is useful only if repeated averaging is actually the intended model.

Iteration is not a good choice merely because it makes a warning disappear. Before relying on it, verify that the values converge, that the result is stable enough for the task, and that users understand the setting. If changing an input makes the result oscillate or keep changing, iteration may not be suitable. For business-critical calculations, document the assumptions and compare the iterative result with an independent calculation where possible.

Setting iteration in Excel for Windows and Mac

On Windows desktop Excel, open File > Options > Formulas, then select Enable iterative calculation under Calculation options. Excel provides a maximum number of iterations and a maximum change. The iteration limit caps how many recalculations Excel performs; the change threshold determines when the difference between successive results is small enough to stop. A higher iteration limit does not guarantee a correct answer, and an excessively loose maximum change can stop the calculation before it is precise enough.

On Mac, open Excel > Settings > Calculation in current releases, or Excel > Preferences > Calculation in older releases, and enable iterative calculation. The wording and layout can vary by release. If a workbook is shared with Excel for the web or other desktop versions, confirm the behavior in the actual environment where it will be used; do not assume every version offers the same controls for changing calculation settings.

Iteration settings can affect calculations across a workbook, not just the one formula you are investigating. Record the intended limits and tell other users why iteration is enabled. If a workbook contains an accidental circular reference as well as an intentional one, turning on iteration may make the accidental loop harder to notice. Review all listed circular references before deciding to keep the setting.

Choosing between removing and keeping the loop

For an ordinary budget, inventory list, report, or formula-based total, the safest default is to remove an unintended circular reference. Keep one only when the model explicitly requires iterative calculation and the result has been checked. A useful decision sequence is:

  1. Confirm that the cycle is intentional and identify the formulas that form it.
  2. Check whether the same calculation can be redesigned with separate inputs and outputs.
  3. If iteration is necessary, test representative inputs and confirm that results settle within an acceptable tolerance.
  4. Document the setting, assumptions, and expected behavior so another user does not mistake the loop for an error.

For many calculations, a helper column, a separate assumption cell, or a staged calculation is easier to audit than iteration. This approach makes dependencies explicit and avoids relying on workbook-level calculation settings. It can also make it easier to compare intermediate results and identify which input is causing an unexpected total.

Frequently Asked Questions

Does a circular reference mean my Excel formula is invalid?
No. The formula can be valid Excel syntax but still create a dependency loop. Excel is warning that it cannot calculate the relationship as a normal one-pass formula.

Why does Excel show a number even though there is a circular reference?
The cell may display an initial or previously calculated value, or the workbook may have iterative calculation enabled. Check the circular-reference warning and calculation settings before relying on the number.

Can a circular reference involve more than one worksheet?
Yes. A formula on one sheet can depend on a cell on another sheet that eventually refers back to the first formula. Follow the references across sheets rather than checking only the active worksheet.

Will enabling iterative calculation fix every circular reference?
No. It permits repeated calculation, but it does not establish that the formula logic is correct or that the results converge. Use it only when iteration is part of the intended model.

Why can I not find the circular reference listed in Excel?
The menu can point to one cell in the loop, while the cause may involve other cells or sheets. Check the status bar, inspect the listed cell’s precedents, and trace the references through the workbook. If needed, open the workbook in desktop Excel for the full Formula auditing and calculation controls.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.