Circular Reference Warning in Excel: Find and Fix It

A circular reference warning in Excel 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, open Formulas > Error Checking > Circular References, select a listed cell, and correct the formula or its references; enable iterative calculation only when the circular calculation is intentional.

What a circular reference means

Excel normally calculates a formula by following the cells it depends on. For example, a total in one cell can add values from other cells. A circular reference breaks that one-way flow: the formula’s result depends, eventually, on the result it is trying to calculate. The loop might be in one formula or span several cells, worksheets, or both.

A direct circular reference occurs when a formula points to its own cell. With A1 selected, this formula makes A1 depend on itself:

With the formula entered in A1, =A1+1 refers directly to A1. Excel cannot calculate a normal result because the value would need to be known before Excel could determine that same value.

An indirect circular reference uses a chain. With A1 and B1 as the two cells in the loop, enter =B1+1 in A1 and =A1+1 in B1. Each cell depends on the other, so neither has an independent starting result. A circular reference can involve a much longer chain, making it less obvious than these examples.

What Excel does when it detects one

When you enter a formula that creates a circular reference, Excel usually displays a warning dialog. Depending on the workbook and calculation settings, it may also show a circular-reference message or cell address on the status bar. If iterative calculation is off, Excel cannot resolve the loop as an ordinary formula calculation; a displayed value may be zero, blank, or an earlier calculated value rather than a meaningful answer. Do not assume that a number appearing in the cell proves the formula is correct.

Excel may not reveal every part of a complicated loop in one step. The Circular References list helps you locate involved cells, but you may need to inspect the formulas and follow their dependencies. A warning can also appear after edits to a workbook that already contained a circular reference, so consider whether the loop is new or intentional before changing formulas.

How to find the cell or formula causing the warning

On Windows in Excel for Microsoft 365, start with the built-in list:

Open the Formulas tab.
Choose Error Checking in the Formula Auditing group, then point to Circular References.
Select a cell address from the submenu to move to that cell. If the submenu is unavailable, Excel may not currently identify a circular reference in the active workbook.
Read the formula in the formula bar and check which cells it uses. Follow those references through other formulas if needed.

For a harder-to-follow chain, select a cell and use Formulas > Trace Precedents to show cells that feed into its formula. Trace the dependencies on the cells in the suspected loop, then remove the arrows with Formulas > Remove Arrows. These arrows are a visual aid, not an automatic circular-reference repair tool. Also check formulas on other sheets, named ranges, and formulas filled down or across: a relative reference can shift in an unexpected way when copied.

If a cell address is listed but the formula looks harmless, inspect the referenced cells rather than only the selected cell. For example, a total formula might point to a helper cell whose formula points back to the total. In a large workbook, search for the cell address in formulas, and check whether the formulas use whole-column or whole-row ranges that include their own result cell.

Common causes and how to fix them

A frequent cause is including a total cell inside the range it is meant to total. With sales values in B2:B10 and the total intended for B11, this formula in B11 includes itself:

With sales in B2:B10 and the total in B11, =SUM(B2:B11) creates a direct circular reference because B11 is part of the sum. Use =SUM(B2:B10) in B11 instead. If the values in B2:B10 are 40, 55, 35, 60, 45, 50, 70, 30, and 65, the corrected formula returns 450.

Other common causes include an incorrect cell address, a formula copied into a row where its range shifted, or a chain of formulas that unexpectedly returns to the original cell. A formula may also refer to itself through a defined name, a table calculation, or a formula on another worksheet. Use the following checks to correct the underlying logic:

Confirm that a sum, average, or other aggregate excludes the cell containing the result.
Check the start and end of ranges, especially after inserting rows or copying formulas.
Follow references across every worksheet involved instead of assuming the issue is confined to the visible sheet.
Decide what the formula is supposed to calculate, then separate the input, intermediate calculation, and output into distinct cells where practical.
After editing, check the Circular References submenu again and confirm that the warning has cleared and the resulting value makes sense.

For the sales example, putting the total below the data and summing only the rows above it is clearer than trying to make a formula in the data range exclude itself. A separate helper cell can also make a complicated calculation easier to review.

When iterative calculation is appropriate

Some models deliberately use circular references to approximate a result through repeated calculations. In that case, Excel’s iterative calculation setting allows it to recalculate formulas repeatedly until a stopping condition is reached. This is different from fixing an accidental loop: enabling iteration can make an unintended circular formula produce a number without making the workbook’s logic correct.

To turn on the setting in Excel for Microsoft 365 on Windows, choose File > Options > Formulas. Under Calculation options, select Enable iterative calculation. You can set Maximum Iterations, which limits the number of calculation cycles, and Maximum Change, which controls the permitted change between results. Excel’s commonly documented defaults are 100 iterations and 0.001 maximum change; the workbook may have different values.

Iteration can stop when Excel reaches the iteration limit or the change between results becomes small enough, depending on the settings and calculation behavior. The result can therefore depend on the starting values and chosen limits. Before enabling the option, understand why the loop is necessary, check that the results converge to a sensible value, and document the assumption for anyone who uses the workbook. Iterative calculation affects calculation behavior for the workbook, so review its implications before sharing the file.

On Excel for Mac, calculation preferences are available through Excel’s settings or preferences, but the exact wording and location can vary by release. Excel for the web has different feature and settings support from the Windows desktop app; if you need to change desktop calculation options, open the workbook in the desktop version. Regardless of platform, enabling iteration should be a deliberate modeling choice, not the default response to a warning.

How to tell an intentional loop from a mistake

A circular reference is not automatically proof that a workbook is broken, but most warning cases arise from an unintended range or reference. Ask whether the calculation genuinely needs a result to feed back into its own inputs. If not, remove the loop. A formula that calculates a total from a range containing its own cell is almost always a range-definition mistake, not a reason to turn on iteration.

For an intentional iterative model, verify the design independently. Check the formulas in the loop, the iteration limit, and the maximum change. Try plausible starting values and confirm that the result settles rather than oscillating or changing substantially when settings are adjusted. If a workbook is used for business reporting or decisions, make the iterative assumptions visible and have the calculation reviewed by someone who understands the model.

Related errors and useful distinctions

A circular reference warning is not the same as #REF!. A circular reference means Excel can follow references that lead back to the formula; #REF! means a formula contains an invalid reference, often because a referenced cell or range was deleted. The remedies differ: repair the broken reference for #REF!, but trace and remove or intentionally iterate a loop for a circular reference.

It is also different from a formula that simply produces an unexpected value. A result can be wrong because of an incorrect range, data type, or assumption without any circular reference. Conversely, a circular formula might display a plausible-looking number under iterative calculation. Check both the dependency structure and the actual calculation logic instead of relying only on whether Excel displays an error.

Frequently Asked Questions

How do I find all circular references in Excel?
On Windows, choose Formulas > Error Checking > Circular References and inspect the listed addresses. Select each address, follow its formula dependencies, and repeat the check after fixing a loop. A complex loop may require tracing cells across worksheets.

Why does Excel show a circular reference when the formula does not refer to itself?
The formula may refer to another cell that eventually refers back to it. Check the formulas in its precedents, including formulas on other sheets and named ranges. The loop can contain several cells even when the original formula has no direct self-reference.

Can I ignore the circular reference warning?
Only if the loop is intentional and the workbook is designed to use iterative calculation. Otherwise, ignoring it can leave an incorrect, incomplete, or misleading value. Find out why the reference loops before deciding what to do.

Why is the Circular References menu empty?
Excel may not currently detect a circular reference in the active workbook, or the warning may have come from another workbook or from a formula that has since changed. Confirm that the correct workbook is active and review the formulas implicated by the original warning.

Will turning on iterative calculation fix the formula?
No. It permits Excel to recalculate circular formulas repeatedly; it does not correct a mistaken cell reference or range. Use it only when repeated calculation is part of the intended model and the result has been checked.

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.