Excel Circular Reference Warning: Causes and Fixes

An Excel circular reference warning means a formula refers to its own cell, either directly or through a chain of other formulas. To find the loop in Excel for Microsoft 365 on Windows, select Formulas > Error Checking > Circular References, open a listed cell, and change the formula so its calculation no longer depends on itself; enable iterative calculation only when the loop is intentional.

What the warning means

A normal formula takes information from other cells and returns a result in its own cell. A circular reference breaks that one-way pattern: Excel would need the cell’s result to calculate the formula, but it also needs the formula to calculate the result. The workbook has no ordinary, one-pass answer for that dependency.

The loop can be direct or indirect. A direct circular reference is a formula that refers to its own cell. An indirect one involves two or more cells: for example, A1 depends on B1 while B1 depends on A1. The warning is about this dependency loop, not necessarily a mistake in the formula’s spelling or punctuation.

Excel may display a warning when you enter the formula, and the status bar can identify a circular reference while the relevant sheet is active. Depending on the workbook and calculation settings, the affected cell may display zero, a previously calculated value, or an iterative result. Do not treat a displayed number as proof that the formula is correct.

How circular references happen

Many circular references are accidental. A common cause is including a total cell in the range it is meant to total. Another is typing a formula into the same cell that the formula is supposed to use as an input. Copying formulas, inserting rows, and changing references can also create an indirect loop that is not obvious from the cell where the warning first appears.

Self-included total: A total formula in A6 sums A2:A6, so it includes its own result.
Two-cell loop: A1 refers to B1, and B1 refers back to A1.
Longer dependency chain: A formula depends on another cell that eventually depends on the original formula.
Copied formula: Relative references shift during copying and may point back into a total or calculation cell.

Some financial and engineering models intentionally use circular calculations, such as a calculation where interest depends on a balance that itself includes interest. Those models require a deliberate method for resolving the loop; a warning is not, by itself, evidence that the circular reference is intentional or safe.

Find the circular reference in Excel

In Excel for Microsoft 365 on Windows, start with the built-in list. Select Formulas > Error Checking > Circular References. If Excel has found a reference, the submenu lists a cell address. Choose an address to move to that cell, then inspect its formula in the formula bar. If there are several listed cells, check each one: fixing one loop may reveal or leave another.

If the menu does not show an address, check the status bar at the bottom of the Excel window and make sure you are looking at the right worksheet. A circular reference may be on a different sheet from the cell you are currently inspecting. Also review formulas in cells involved in the calculation, rather than assuming the first cell Excel highlights is the only cause.

For a more complicated model, use Excel’s formula-auditing tools. Select a formula cell, then choose Formulas > Formula Auditing > Trace Precedents to display arrows to cells used by that formula. Choose Trace Dependents to see cells that rely on the selected cell. Follow the arrows through the relevant cells until the chain leads back to its starting point. Use Remove Arrows in the same Formula Auditing group when you are done.

It can help to write down the loop in plain language before editing. For instance, “monthly total uses the tax cell; tax uses monthly total” makes the dependency problem clearer than looking at several formula bars in succession. If formulas refer to other worksheets, follow those references too.

Repair an accidental loop

First decide what each cell is supposed to represent. Separate inputs, intermediate calculations, and final outputs where possible. A formula should usually refer to input or intermediate cells, not to its own output cell. Then correct the range or reference and check the result against a small hand calculation.

With amounts 120, 80, 50, and 25 in A2:A5, the intended total belongs in A6. If the formula in A6 is =SUM(A2:A6), its range includes A6 itself, so Excel reports a circular reference and cannot produce a normal total. Change the formula in A6 to =SUM(A2:A5); it returns 275, because 120 + 80 + 50 + 25 = 275.

Another way to reduce accidental loops is to keep source values and calculated results in distinct cells. For example, store a rate in one cell and the calculated amount in another, rather than overwriting an input with a formula that depends on that input. Before deleting or changing a referenced cell, check its dependents so you do not replace the circular reference with a different error or an incorrect calculation.

When iterative calculation is appropriate

Excel can calculate some intentional circular models by repeatedly recalculating formulas until they meet a stopping rule. This is called iterative calculation. It is appropriate only when the model is designed for it and you understand what value the repeated calculation should approach. It is not a general repair for an unexplained warning.

For an illustrative model, suppose A1 is designed to feed part of its own result back into the next calculation. With A1 as the only cell in the model, an initial value of 0, and the formula in A1 =A1*0.5+10, repeated calculation approaches 20: each pass halves the remaining gap from 20. With the usual maximum-change setting of 0.001, the displayed result will be approximately 20 rather than necessarily exactly 20. This example shows why the output depends on the iteration settings and starting value.

On Windows desktop Excel, open File > Options > Formulas, select Enable iterative calculation, and review Maximum Iterations and Maximum Change. The common defaults are 100 iterations and 0.001 maximum change. More iterations allow more recalculations; a smaller maximum change asks Excel to continue until successive results are closer. These are stopping controls, not proof that a model is mathematically sound.

Iterative calculation can affect other circular formulas in the workbook, so document why it is enabled and verify important results independently. If the model’s values change unexpectedly, compare the initial values, iteration limit, and change threshold with the model’s requirements instead of simply increasing the limit.

Why the warning can persist

After correcting a formula, check the Circular References menu again and inspect all listed addresses. A workbook can contain more than one loop, and fixing one does not automatically repair unrelated formulas. If a cell still appears in the list, look for a second path back to that cell, including references on other sheets.

Also distinguish a circular reference from other formula problems. A misspelled function, an invalid cell reference, or a value error may produce a different error message and does not necessarily create a dependency loop. Conversely, a circular formula may not show a familiar error value if iterative calculation is enabled or Excel is displaying a prior result. Review the formula logic and the calculation settings together.

When investigating a workbook received from someone else, avoid immediately enabling iteration just to suppress the warning. Save a separate copy before making structural changes, and ask what the workbook is intended to calculate. A result that looks plausible can still be wrong if the loop, starting value, or iteration threshold is inappropriate.

Excel for Mac and Excel for the web

The steps above describe Excel for Microsoft 365 on Windows. In Excel for Mac, look under the Formulas tab for formula checking and auditing commands; ribbon placement and menu labels can vary by release. Iterative calculation is controlled in Excel’s calculation preferences rather than Windows’ File > Options dialog. If a command is not in the same place, use Excel’s command search or the Help menu for the installed version.

Excel for the web has a different interface and does not expose every desktop formula-auditing or calculation setting in the same way. If you cannot locate the circular-reference address or change the iteration settings in the browser, open the workbook in the desktop Excel app and inspect it there. A workbook that uses iteration should be checked in the desktop version and with the settings expected by its author.

Frequently Asked Questions

Is a circular reference always an error?
No. It is often accidental, but some models deliberately use iterative calculation. Treat the warning as a signal to inspect the formula dependencies and confirm whether the loop is intentional.

Where does Excel show the circular reference?
On Windows desktop Excel, select Formulas > Error Checking > Circular References to see listed cell addresses. The status bar may also identify a circular reference. Check every listed cell and any connected formulas across sheets.

Why does my circular-reference cell show zero?
Excel may show zero or another provisional value when a circular formula cannot be resolved through ordinary calculation. The exact display can depend on the workbook’s calculation state and iteration settings, so zero does not mean the formula is valid.

Should I turn on iterative calculation to remove the warning?
Only if the workbook intentionally relies on repeated calculations and you know the appropriate starting values and stopping settings. For an accidental loop, correct the formula instead; enabling iteration can conceal a logic error.

Can a circular reference involve different worksheets?
Yes. A formula on one sheet can depend on a cell on another sheet, whose formula eventually points back to the first. Follow precedent and dependent relationships across sheets, not just within the currently visible worksheet.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.