Microsoft Excel Circular Reference Error: How to Fix It

A Microsoft Excel circular reference error means a formula depends on its own result, either directly or through other cells. Find the formula Excel flags, then change its references so the calculation no longer loops; enable iterative calculation only when the loop is intentional and understood.

What a circular reference means

Excel normally calculates a cell from inputs that do not depend on that cell. A circular reference breaks that pattern: the formula needs a value that cannot be determined until the formula itself has been calculated. For example, a formula in A1 that refers to A1 directly is circular. A formula in A1 that refers to B1 is also circular if B1, in turn, refers to A1.

The reference may be difficult to spot because the loop can pass through several cells, worksheets, or named ranges. A formula such as SUM can also include its own cell by mistake. If a total is placed in B10 but its range is B2:B10, the formula includes its result cell, creating a direct circular reference.

Excel generally warns about a circular reference and may show a zero, a previous value, or another result affected by the workbook’s calculation settings. The displayed value should not be treated as reliable until the loop is resolved or intentionally configured. Circular references are not always errors in the sense of bad design: some financial and engineering models use them deliberately, but they require careful setup.

How to find the formula causing it

In Excel for Microsoft 365 on Windows, start with the warning that appears after you enter or change a formula. Excel may identify a cell address in the status bar. You can also use the ribbon to locate cells Excel has detected:

Open the Formulas tab.
Select Error Checking in the Formula Auditing group.
Point to Circular References. If Excel lists one or more cell addresses, select an address to go to that cell.
Inspect the formula bar and note the referenced cells. Follow those references until you find the path that leads back to the original cell.

If the list does not make the whole loop clear, use Formulas > Trace Precedents and Formulas > Trace Dependents to display arrows between related cells. These tools can help reveal an indirect loop; repeat the tracing from each relevant cell. A workbook may contain more than one circular reference, so resolving the first one does not guarantee that the warning will disappear.

You can also search for likely mistakes in the formula text. Check whether a SUM range includes the cell containing the SUM, whether a copied formula shifted a reference unexpectedly, and whether a cell on another sheet points back to the current sheet. If names are used in formulas, check what cells or ranges those names refer to.

Common causes and examples

A frequent cause is putting a total inside the range it totals. With values in B2:B9 and a total in B10, the total range should stop at B9. With the total entered in B10, the formula =SUM(B2:B9) adds only the values above it. If the formula is instead entered in B10 as =SUM(B2:B10), it includes B10 itself and creates a circular reference.

Another common cause is a mistaken self-reference in a calculation. Imagine a tax or commission formula entered in the same cell it refers to, when the formula was meant to refer to a separate input cell. A formula copied down a column can also shift a reference into its own row. For example, a relative reference that was correct in the first row may become incorrect after filling the formula down.

Indirect loops often involve several calculations that seem reasonable on their own. A1 might calculate a total using B1, while B1 calculates a percentage of A1. Neither formula visibly refers to itself, but together they form a loop. Such cycles can cross worksheet tabs, so inspect the references rather than assuming the problem is confined to the cell that displayed the warning.

How to fix an unintended circular reference

First decide what the calculation is supposed to represent. Then separate inputs from outputs, correct the reference range, or move a total or intermediate calculation to another cell. Do not simply replace the formula with a number unless a fixed value is genuinely intended; doing so can hide the problem and leave the workbook out of date when its inputs change.

Exclude the result cell: shorten a SUM or other range so it does not contain the formula cell.
Correct a cell reference: replace a reference to the formula’s own cell with the intended input or source cell.
Break an accidental dependency: move an intermediate value to a separate cell, then have the final formula use that value.
Check copied formulas: verify relative references row by row and use absolute references where a fixed input is intended.
Review named ranges and other sheets: confirm that indirect references do not lead back to the original formula.

After changing a formula, check Formulas > Error Checking > Circular References again and confirm that the warning is gone. Recalculate if necessary with Formulas > Calculate Now. Also verify the result against a hand calculation or a small test case, especially if the formula feeds other totals.

When iterative calculation is appropriate

Iterative calculation tells Excel to recalculate formulas in a circular chain repeatedly, using the latest results, until it reaches a stopping condition. This can be appropriate when the model intentionally defines a value in terms of another value that depends on it, such as interest calculated on an ending balance that includes that interest. It is not a general-purpose way to clear an error: an accidental circular reference can produce a plausible-looking but wrong result when iteration is enabled.

In Excel for Microsoft 365 on Windows, go to File > Options > Formulas, select Enable iterative calculation, and set Maximum Iterations and Maximum Change. Maximum Iterations limits how many recalculations Excel performs. Maximum Change sets the size of the difference between results that Excel accepts as sufficiently small. Higher iteration limits do not make every model converge, and a tolerance that is too loose can leave an imprecise result.

On Excel for Mac, the calculation preferences are available through Excel’s settings or preferences under Calculation; the exact label can differ by release. Excel for the web does not provide the same desktop controls for setting iterative calculation, so use the desktop app to configure them. If a workbook contains intentional circular formulas, check how it behaves in the version and environment where it will be used.

Before enabling iteration, document which cells form the loop, why the loop is needed, and what result is expected. Test the model with known inputs and check whether changing the iteration limit or maximum change materially changes the answer. If small changes cause large swings, the calculation may be unstable and needs redesign rather than a larger iteration limit.

A worked example of an intentional loop

Suppose A1 contains an opening balance of 100, the annual rate is 5%, B1 is the ending balance, and C1 is interest calculated on that ending balance. The intended relationships are:

With the opening balance in A1, ending balance in B1, and interest in C1, enter this in B1: =A1+C1. With the same layout, enter this in C1: =B1*5%.

Those two formulas refer to each other. If iterative calculation is enabled, they converge toward an ending balance of about 105.26 and interest of about 5.26. The arithmetic follows B = 100 + 0.05B, so 0.95B = 100 and B is approximately 105.263. Excel’s displayed value depends on its iteration and change settings. This is an illustration of the mechanics, not financial advice; real account terms can calculate interest differently.

If interest is meant to apply only to the opening balance, no circular calculation is needed. With 100 in A1 and the interest result in C1, enter =A1*5% in C1; it returns 5. With the ending balance in B1, enter =A1+C1 there; it returns 105. The right model depends on the intended definition, not on which formula is easiest to enter.

How to prevent the error in future workbooks

Plan the worksheet so inputs, intermediate calculations, and final outputs are easy to distinguish. Keep totals outside the ranges they summarize, and label cells clearly. When copying formulas, check both the first and last rows rather than assuming the references remain correct. For a complex model, trace a few key calculations before sharing it.

Be cautious when changing a workbook’s calculation settings. Iterative calculation can affect other circular formulas in the workbook, not just the cell currently being investigated, and can make results harder for another person to interpret. Add a note explaining intentional loops and their settings. When practical, rewrite the calculation algebraically to remove the loop; a direct formula is often easier to audit and more portable across spreadsheet apps.

Frequently Asked Questions

Is a circular reference always an error?

No. It is an error when the loop is accidental or when Excel cannot produce the intended result. Some models deliberately use a circular relationship and enable iterative calculation, but the formulas and stopping settings should be checked and documented.

Why does Excel show a circular reference when I cannot see one?

The loop may pass through other cells, worksheets, or named ranges rather than pointing directly back to itself. Use the Circular References list, then trace precedents and dependents through the chain until a reference returns to the starting cell.

Why is my total formula circular?

Often the total cell is included in its own SUM range. For example, a total in B10 should not use a range that extends through B10. Check the formula’s start and end cells and exclude the cell containing the total.

Should I turn on iterative calculation to remove the warning?

Only if the circular relationship is intentional and the workbook’s logic requires repeated calculation. For an accidental loop, correct the formulas instead. Enabling iteration can make a flawed model return a number without fixing its underlying logic.

Can Excel for the web fix circular references?

You can inspect and edit formulas in Excel for the web, but its calculation controls do not match the desktop options for configuring iterative calculation. Use Excel for Windows or Mac when you need to set or review those iteration settings.

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.