Excel Circular Reference Error: Find and Fix It

An Excel circular reference warning means a formula refers to its own cell, either directly or through other formulas. To fix an accidental loop in Microsoft Excel for Microsoft 365 on Windows, go to Formulas > Error Checking > Circular References, select a listed cell, and edit the formula so its calculation no longer leads back to itself.

What the circular reference message means

Excel may display a warning that says, "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly." The wording can vary slightly between Excel versions, but the key point is the same: a formula depends on its own result. Excel cannot calculate an ordinary formula when it needs that result before it can produce it.

A circular reference is not necessarily a typing error in the formula. It is a dependency problem: Excel’s calculation engine follows the cells needed to evaluate a formula and eventually arrives back at the original cell. The loop can involve one cell, several cells on the same worksheet, or cells on different worksheets. It may be intentional in a model designed to calculate repeatedly, but most spreadsheet formulas should not form a loop.

The warning is also different from a standard cell error such as #VALUE! or #REF!. A circular reference may leave a cell displaying zero, a previous value, or a result that appears plausible, depending on calculation settings and workbook history. A number showing in the cell does not prove that the formula is correct.

How a circular reference works

A direct circular reference occurs when a formula refers to the cell that contains it. For example, if cell A1 contains =A1+1, Excel must know A1’s value to calculate A1. There is no independent starting value, so the formula points back to itself immediately.

An indirect circular reference takes more than one step. Suppose A1 depends on B1 and B1 depends on A1. To calculate A1, Excel needs B1; to calculate B1, it needs A1. The two formulas form a loop even though neither formula mentions its own cell by name. A longer chain can involve many cells, and the final link may be on another worksheet.

References inside functions count too. A total formula can accidentally include its own cell in the range being totaled. With a total intended for B11 and source amounts in B2:B10, the correct layout keeps the result outside the summed range. If the total is mistakenly placed in B2 and includes B2:B10, the formula includes its own result. The issue is the overlap between the formula cell and its input range, not the SUM function itself.

Find the cell causing the warning

Use Excel’s built-in list before searching formulas manually. On Windows in Microsoft 365, open Formulas > Error Checking > Circular References. The dropdown lists cells Excel has identified as part of a circular reference. Select a listed address to move to that cell, then inspect the formula bar and the surrounding inputs.

Check the active worksheet and the cell address shown in the Circular References list.
Read the formula bar and identify every cell or range the formula uses.
Follow those references to see whether one points back to the original cell, directly or through another formula.
After correcting a loop, check the Circular References list again; Excel may reveal another one that was not obvious at first.

The status bar at the bottom of the Excel window can also show a Circular References indicator and a cell address. If you do not see it, the warning may have been dismissed, the status bar may not be displaying that information, or you may need to use the Formulas menu. A circular reference can exist on a worksheet other than the one currently open, so do not assume the visible sheet contains the only cause.

For a complicated workbook, inspect formulas in likely input and result cells and follow the chain one link at a time. Look especially at totals, balances, percentages, and formulas copied across rows or columns. A formula copied from a neighboring row can acquire a shifted reference that points into its own output. If the warning appeared after a recent change, undoing that change or comparing the affected formula with a nearby working formula can quickly expose the mistake.

Common causes and mistaken assumptions

One frequent cause is placing a total inside the range it totals. Another is an incorrect cell reference after copying a formula, inserting rows, or changing a layout. A formula may also refer to a cell that appears to be an input but is itself calculated from the first formula. Cross-sheet links can make that dependency less visible: a formula on a Summary sheet may depend on a value on Detail, while a Detail formula depends in turn on Summary.

Watch for these patterns:

A SUM, AVERAGE, or other range calculation includes the cell containing that calculation.
A running balance formula refers to a later balance cell, creating a chain that loops back.
Two or more formulas refer to each other, sometimes across worksheets.
A copied formula uses a relative reference that shifted into the formula’s own row or column.
A deliberate model change creates a dependency that was not present in the original workbook.

A common misconception is that any formula using the same column as its output is circular. It is not. A formula in B11 can safely total B2:B10 because B11 is outside the input range. What matters is whether the formula’s actual precedent chain leads back to the formula cell. Similarly, a cell can reference itself through a named range or a formula on another sheet, so checking only the visible cell address may not be enough.

Fix an accidental circular reference

The right repair is to separate inputs from outputs or correct the incorrect reference. Do not enable iterative calculation simply to make an accidental warning disappear: that changes how Excel calculates the workbook and can conceal a bad formula. First decide what each formula is supposed to calculate, then check that its inputs do not depend on its result.

For a total intended in B11, with the values to add in B2:B10, enter =SUM(B2:B10) in B11. The formula adds nine cells and does not include B11, so it has no direct self-reference. If the intended total was accidentally entered in B2, move it to a separate output cell or change the range to contain only the true input cells.

For a running balance, decide which row contains the starting balance and which rows contain transactions. Each balance should use the prior balance and the current transaction, not a later balance that depends on it. If a formula is copied down, check the first and last rows separately; boundary rows often need different references from the rows in the middle.

When you find a circular reference, use this repair checklist:

Write down the intended inputs and the intended output cell.
Remove the output cell from any input range that includes it by mistake.
Correct references that point to the wrong row, column, or worksheet.
Recalculate and verify the result against a simple manual check or known example.
Review the Circular References list again to confirm that no unintended loop remains.

When iterative calculation is appropriate

Some specialized models intentionally use a circular reference to repeat a calculation until its value settles, a process called iterative calculation. For example, a model may need a result that feeds back into an assumption, with each pass refining the result. This is different from an ordinary formula error: the workbook’s design must explain why the loop exists and what result the repeated calculation should approach.

In Excel for Microsoft 365 on Windows, open File > Options > Formulas, select Enable iterative calculation, and set Maximum Iterations and Maximum Change if the model requires different limits. Maximum Iterations limits how many calculation passes Excel makes; Maximum Change sets the threshold used to decide that successive results have changed by little enough. The defaults are 100 iterations and 0.001 maximum change, but appropriate settings depend on the model.

Do not turn this setting on without understanding the formulas involved. Iteration can produce an approximate result, and results can depend on starting values, calculation order, and the selected limits. If the workbook is shared, document why the circular design is intentional and record the settings needed to reproduce its results. On Mac, calculation preferences are available through Excel’s application settings, commonly under Excel > Preferences > Calculation or Excel > Settings > Calculation, depending on the release. Menu labels can differ by version.

Check results after making a change

Once the loop is removed, confirm that the affected cells recalculate and that dependent totals or summaries still make sense. Excel normally recalculates formulas automatically, but a workbook may be set to manual calculation. On Windows, check Formulas > Calculation Options and select Automatic if that is appropriate for the workbook. Manual calculation can leave displayed results out of date; it does not, by itself, repair a circular reference.

Use a small known example to validate the result. If a total should equal the sum of a few visible numbers, add those numbers independently and compare. If the workbook is important, save a copy before changing formulas, particularly when editing a complex model or one with intentional iteration. This gives you a way to compare the original logic if the change has unexpected effects.

Frequently Asked Questions

How do I find all circular references in Excel?
On Windows, choose Formulas > Error Checking > Circular References and select each listed cell. Fixing one loop can reveal another, so check the list again after each repair.

Why does Excel show a circular reference when I do not see one?
The loop may be indirect, involve another worksheet, or use a named range. Follow the formula’s precedents rather than looking only for a cell that mentions itself. Also check copied formulas and ranges that overlap their output cells.

Can a circular reference be intentional?
Yes. Some iterative models deliberately feed a result back into its calculation. Enable iterative calculation only when the model requires it and you understand the iteration limits; otherwise, remove the accidental dependency.

Does a circular reference always display an error in the cell?
No. Excel may show a warning without placing a familiar error code in the cell. A displayed number can be zero, an old value, or an iterative result, so inspect the formula and workbook calculation settings.

Will turning on iterative calculation fix the formula?
Not necessarily. It permits Excel to calculate circular formulas repeatedly, but it does not tell Excel whether the loop was intended or whether the result is meaningful. Correct accidental references instead of using iteration as a workaround.

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.