Disable Circular Reference Warnings in Excel Safely

To stop Excel from showing a circular-reference warning for an intentional circular formula, enable iterative calculation: in Excel for Microsoft 365 on Windows, go to File > Options > Formulas, check Enable iterative calculation, then select OK. Excel has no separate switch that merely hides the warning: enabling iteration allows circular formulas to calculate, so first make sure the circular reference is deliberate and stable.

What a circular reference means

A formula has a circular reference when its calculation depends on its own result. The reference can be direct, as when a formula in A1 refers to A1, or indirect, when one formula depends on another that eventually leads back to the first. For example, A1 might depend on B1, B1 on C1, and C1 on A1. Excel cannot calculate that dependency in the usual one-pass order because each value is waiting for another value in the same cycle.

When you enter or edit a formula that creates a circular reference, Excel normally displays a warning. Depending on the workbook and calculation settings, it may also show a circular-reference indication in the status bar. The warning is a signal to check the formula, not proof that the workbook is damaged: some models deliberately use repeated calculations to reach a stable value.

It is important to distinguish hiding a warning from fixing a circular reference. If the formula refers to itself by mistake, enabling iteration may make the warning go away while leaving the underlying calculation wrong. If the loop is intentional, iteration can be appropriate, but the result depends on the workbook’s iteration limits and whether the calculation converges.

Enable iterative calculation in Excel for Windows

Use the desktop app for Microsoft 365 on Windows and follow these steps:

Open the workbook that contains the circular formula.
Select File > Options > Formulas.
Under Calculation options, select Enable iterative calculation.
Review Maximum Iterations and Maximum Change before accepting the setting.
Select OK, then check the affected cells and the workbook’s other calculations.

There is not a separate setting labelled disable circular-reference warning. Enabling iterative calculation tells Excel to recalculate circular formulas repeatedly under specified stopping conditions instead of treating the loop as an ordinary calculation error. It can therefore suppress the warning for a deliberate loop, but it changes calculation behavior as well as the message you see.

To undo the change, return to File > Options > Formulas and clear Enable iterative calculation. If circular formulas remain, Excel may warn about them again or stop calculating them as intended. Do not turn the setting off until you understand whether the workbook relies on iteration.

Choose iteration limits carefully

Excel uses two controls to decide when to stop iterating. Maximum Iterations limits how many calculation rounds Excel performs. Maximum Change sets the largest allowed difference between successive results before Excel considers the calculation sufficiently stable. The defaults are commonly 100 iterations and 0.001 maximum change, but check the values shown in your version and workbook rather than assuming they have not been changed.

A simple illustrative example shows why the change threshold matters. Assume A2 contains the circular formula below, starts at 0, and the maximum change is 0.001. Each round moves the result halfway toward 10; after about 14 rounds, the change between rounds is within that threshold, and the displayed result is approximately 9.9994.

With the formula in A2 and an initial value of 0, enter =(A2+10)/2. The formula refers to its own cell, but repeated results approach 10 rather than growing without limit. This is a teaching example, not a recommended model design; check the workbook’s actual limits and starting conditions when evaluating a real formula.

A higher maximum-iteration limit allows more rounds but can make recalculation take longer.
A smaller maximum-change value asks for a closer result, which may require more rounds.
If the calculation does not settle before reaching the iteration limit, the last calculated value may not be a reliable result.

Iteration settings are not a per-cell warning preference. Other circular formulas in the workbook may also be recalculated iteratively, so inspect the workbook as a whole. For a model where the result matters, document the chosen limits and test how the output responds to different inputs.

When iteration is appropriate

Iteration can be useful when a model intentionally needs a repeated calculation and the values converge to a sensible result. A common class of examples is a business model with a circular dependency between two calculated amounts, such as a value that affects a charge while that charge also affects the value. Some accounting and engineering workbooks also use controlled iterative calculations. Whether a particular model should use this method depends on its logic and requirements.

A well-behaved loop generally approaches a stable result: successive calculations get closer together, and small changes to the inputs produce reasonable changes in the output. A loop that grows without limit, alternates between values, or changes substantially when the iteration limit is adjusted needs investigation. Iterative calculation does not make every circular formula valid; it only gives Excel a way to repeat calculations.

Before enabling iteration for a model, ask:

Is the circular dependency intentional and documented?
Does the calculation converge for typical inputs and edge cases?
Is the chosen maximum change suitable for the precision the workbook needs?
Can the model be redesigned to calculate the same result without a circular dependency?

Fix accidental circular references instead

If the loop was not intended, remove the circular dependency rather than suppressing its warning. In Excel for Windows, select Formulas > Error Checking and open the arrow next to that command. If Excel has identified circular references, use the Circular References list to jump to a listed cell. Correct the formula, then check the list again: a workbook can contain more than one circular reference, and resolving one may expose another.

Trace the formula’s precedents and dependents to understand the chain. On the Formulas tab, the Trace Precedents and Trace Dependents tools can help show which cells feed into or rely on the selected cell. Check copied formulas carefully: a relative reference that shifted during copying can accidentally point back to the formula’s own row or column. Also review named ranges and formulas on other sheets, since the cycle may not be obvious from the cell’s immediate formula.

Common repairs include correcting a cell reference, moving a total outside the range it sums, or separating an input from a calculated output. For example, if a total formula is placed inside the range it totals, move the total cell or adjust the range so it no longer includes itself. After making a repair, verify the result against a known value or a small hand-calculated example.

Warnings, errors, and common confusion

A circular-reference warning is not the same as Excel’s general error values such as #VALUE! or #REF!. It identifies a dependency loop. Once iteration is enabled, Excel may display a number in a cell that previously had no useful result, but that does not establish that the number is correct. A plausible-looking result can still be stale, unconverged, or based on an incorrect formula.

Another common misunderstanding is to change calculation mode to manual as a way to eliminate the warning. Manual calculation controls when Excel recalculates; it does not repair a circular formula or replace iterative calculation. It can also leave displayed results out of date until a recalculation is requested. Keep automatic calculation unless there is a deliberate reason to use another mode, and check Formulas > Calculation Options if results do not update as expected.

If the warning persists after enabling iteration, confirm that the setting was applied to the workbook you are editing, check whether the formula is still circular, and review the iteration limits. If a workbook behaves differently on another computer, compare its calculation settings and Excel version. Do not assume that hiding a dialog means the cycle has been solved.

Excel for Mac and Excel for the web

In Excel for Mac, the setting is available in the desktop app under Excel > Settings (called Preferences in some older releases), then Calculation. Select Use iterative calculation and review the available iteration controls. The wording and placement can vary slightly by Mac release, but look in Excel’s calculation settings rather than in a cell’s formula menu.

Excel for the web does not present the same full desktop Options dialog. If you need to change iteration settings, open the workbook in the desktop version of Excel and use the Windows or Mac path above. A workbook opened in a different Excel environment may calculate differently if its settings or supported behavior differ, so verify important results in the version used for the model.

Frequently Asked Questions

Can I turn off only the circular-reference warning?
Excel does not provide a standalone preference that hides this warning while leaving circular formulas otherwise untouched. For an intentional loop, enable iterative calculation and check its limits. For an accidental loop, correct the formula.

Where do I find the circular reference in Excel?
On Windows, open Formulas > Error Checking, then check the Circular References list. Select a listed cell to go to it. Repair that dependency and repeat the check, because there may be additional loops.

Will enabling iterative calculation affect every formula?
It does not alter every formula, but it can affect circular formulas beyond the one that prompted the warning. Review other circular references in the workbook and verify their results after changing the setting.

Why does Excel still show the warning after I enabled iteration?
Check that you changed the setting in the correct workbook and that Enable iterative calculation remains selected. Then inspect the listed circular references and confirm that the workbook is open in a desktop Excel version that exposes the setting. The formula may still be faulty even if iteration is enabled.

Is it safe to leave iterative calculation on?
It can be appropriate for a workbook designed to use convergent circular calculations, but it is not a general-purpose warning fix. Check that the loops converge, that the stopping limits suit the required precision, and that the resulting values remain correct as inputs change.

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.