Disable Circular References in Excel: Causes and Fixes

To stop Excel for Microsoft 365 on Windows from calculating circular formulas iteratively, go to File > Options > Formulas and clear Enable iterative calculation. To remove a circular reference itself, edit the formula so it no longer refers, directly or indirectly, to the cell containing it; turning off iteration does not delete or repair the formula.

What a circular reference means

A formula has a circular reference when its result depends on itself. The dependency can be direct, as when a formula in A1 refers to A1, or indirect, when a chain of formulas eventually leads back to the starting cell. Excel normally warns about these loops because it cannot resolve them as a straightforward sequence of calculations.

For a direct example, if A1 contains =A1+1, the value of A1 depends on the value Excel is trying to calculate for A1. For an indirect example, suppose A1 contains =B1+1 and B1 contains =A1-1: A1 depends on B1, while B1 depends on A1. In both cases there is no independent starting value for the calculation.

Circular references are not the same as the #REF! error. A #REF! error generally means a formula has an invalid cell reference, often because a referenced row, column, or cell was deleted. A circular reference is a loop in otherwise recognizable references. Excel may show a circular-reference warning or indicator rather than displaying #REF!.

What disabling the setting actually does

Excel’s Enable iterative calculation option determines whether it will repeatedly recalculate formulas that depend on themselves. With the option off, Excel does not intentionally iterate through a circular calculation to approach a result. The circular formulas remain in the workbook, however, and Excel can still identify them as circular references.

This distinction matters: there is no single switch that automatically rewrites every loop into a correct formula. Turning off iteration is useful when a workbook should not use circular calculations, or when you want Excel to flag accidental loops instead of attempting to calculate them. If the loop is a mistake, the lasting fix is to correct the references or the calculation design.

When a circular formula is left in place with iteration disabled, its displayed result may be zero, a previous value, or otherwise not useful, depending on the workbook and calculation state. Do not treat a displayed number as proof that the formula is correct. Check the circular-reference warning and inspect the formula.

Turn off iterative calculation in Excel for Windows

Open the workbook in the Excel desktop app.
Select File > Options.
Select Formulas in the Excel Options window.
Under Calculation options, clear Enable iterative calculation.
Select OK, then check the workbook for circular-reference warnings and unexpected results.

The workbook can retain the circular formulas after you clear the setting, so review the formulas rather than assuming the problem has been removed. Iterative calculation settings are associated with the workbook, and opening a different workbook may involve different calculation settings. If the workbook is shared, make sure its intended calculation behavior is understood before changing the setting and saving it.

Find the cells involved in a circular reference

On Windows, open the Formulas tab and select Error Checking > Circular References. When Excel has detected a circular reference, this menu can show a cell address; select an address to go to that cell. If there are several loops, inspect the listed cells one at a time. Fixing one may expose another, so check the menu again after editing.

Excel may also display a circular-reference notice on the status bar, sometimes with a cell address. Select or inspect the named cell, then read its formula in the formula bar. If the reference crosses worksheets, follow each referenced cell until you find the link that returns to the original formula. The Formulas tab also includes Trace Precedents and Trace Dependents, which can help show relationships among cells. These tools show links, but you still need to determine which link is unintended.

If the menu does not point clearly to the source, check recently edited formulas, copied formulas, defined names, and formulas on other sheets. A circular path can be indirect, so a cell that looks harmless by itself may depend on another cell that eventually depends on it.

Common accidental causes and practical fixes

A frequent mistake is including a total cell inside the range it is meant to total. For example, if A10 is the total and the values to add are in A1:A9, putting =SUM(A1:A10) in A10 includes A10 in its own calculation. With values in A1:A9 and the total in A10, use =SUM(A1:A9) instead. This keeps the total outside the range being added.

Other common causes include:

Entering a formula in the wrong row or column. A formula intended for a neighboring cell may refer back to its own location after being copied or edited. Check the formula bar and confirm the references match the intended layout.
Cross-sheet dependencies that loop back. A formula on the Summary sheet may refer to Detail, while a formula on Detail refers back to Summary. Follow the chain across sheets and decide which sheet should supply the independent input.
A self-referential running total. A running total should normally build from a prior total and the current period’s value, not include the current total cell in its own input range. Check copied formulas row by row, especially at the first and last rows.
Indirect references or defined names. A formula using a defined name or a reference-building function can hide the path from view. Inspect the name’s definition and the cells it resolves to, not only the visible formula text.

When repairing a loop, identify the intended inputs and outputs first. A total should usually depend on detail cells; a detail cell should not depend on the total it contributes to. If the circularity arose from a mistaken range, adjust the range. If it arose from a design that feeds a result back into an input, separate the input, intermediate calculation, and final output into distinct cells.

When iterative calculation is intentional

Some models deliberately use circular calculations. For example, a workbook may calculate a value that feeds back into another calculation and repeat the process until the result changes by a sufficiently small amount or Excel reaches an iteration limit. In that situation, turning off iterative calculation can prevent the model from producing its intended result.

With iteration enabled, Excel uses limits such as the maximum number of iterations and the maximum change allowed between results. These are calculation controls, not a guarantee that a circular model is mathematically appropriate or will converge to a meaningful answer. A result can depend on the chosen starting values and settings. If you do not know why a workbook uses a loop, ask its author or inspect its documentation before disabling iteration or changing those limits.

For a workbook that should not contain loops, leaving iteration disabled is a sensible way to make accidental circular references easier to catch. For a workbook deliberately built around iterative calculations, keep the setting enabled and document why the loop exists, which cells participate, and what result or convergence behavior is expected.

Excel for Mac and Excel for the web

In Excel for Mac, look for the calculation settings under Excel > Preferences > Calculation; in some current builds, the menu may say Settings instead of Preferences. Clear the iterative-calculation option there. The exact wording and menu presentation can vary by build, but the setting is the Mac equivalent of Enable iterative calculation in Windows desktop Excel.

Excel for the web does not use the Windows File > Options path. If you need to change or verify a workbook’s iterative-calculation settings, open it in the desktop version of Excel, where the relevant calculation controls are available. A workbook containing intentional iteration may behave differently if opened in another app or version, so check important outputs after switching environments.

Check the workbook after making a change

After disabling iteration or editing a formula, recalculate and review the affected results. Use the circular-reference menu again to see whether Excel still identifies a loop. Also compare key totals with an independent check, such as summing the underlying detail values separately or reviewing the formula dependencies. A formula that no longer triggers a warning can still be logically wrong.

If you are unsure whether the workbook depends on iteration, save a separate copy before changing its calculation settings. This gives you a way to compare the original results and restore the original workbook if the change affects a model you did not intend to alter. For a shared file, tell collaborators what you changed, particularly if the workbook contains financial, forecasting, or operational calculations.

Frequently Asked Questions

Can I disable circular references without changing formulas?
You can turn off Enable iterative calculation to stop Excel from iteratively calculating circular formulas. That does not remove the loops. To eliminate an accidental circular reference, change the formula or workbook design that creates it.

Why does Excel show zero after I turn iteration off?
A circular formula has no ordinary independent calculation path, so its displayed value may not be meaningful when iteration is disabled. Zero does not confirm that the formula is fixed. Locate the circular reference and correct it, unless the loop is intentional and should use iteration.

Will turning off iterative calculation remove circular-reference warnings?
No. The setting controls iterative calculation; it does not erase the reference loop. Excel can still identify circular formulas, and the warning or status-bar indicator may remain until you fix the loop or handle the workbook’s intended calculation method.

Why can’t I find Circular References on the Formulas tab?
The menu is useful when Excel has detected a circular reference in the active workbook. If the command is unavailable, check that workbook’s formulas, calculation settings, and the desktop app you are using. In Excel for the web, open the file in desktop Excel to use the Windows menu path.

Should I enable iterative calculation to fix a circular reference?
Only if the workbook is deliberately designed to use iterative calculations and you understand its settings. Enabling iteration can make a loop produce a result, but it does not correct an accidental circular reference or prove that a result is reliable.

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.