Allowing Circular References in Excel: When and How

How-to basics · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, go to File > Options > Formulas, select Enable iterative calculation, and choose iteration limits. This lets Excel recalculate formulas that depend on their own results; use it only when the circular calculation is intentional, because it can also hide a formula mistake.

What a circular reference means

A formula has a circular reference when its result depends, directly or indirectly, on that same result. A direct example is a formula in A1 that refers to A1. An indirect example is a formula in A1 that refers to B1 while B1, in turn, refers to A1. Excel cannot resolve either dependency with a single ordinary calculation, because it would need the result before it had finished calculating that result.

Not every formula that refers to a cell involved in the same worksheet is circular. The dependency has to loop back to the formula’s own cell. For example, a total in C2 that adds values from A2 and B2 is not circular unless one of those input cells also depends on C2. A circular reference can arise accidentally after inserting a row, copying a formula, or including a total cell in its own sum.

How to allow iterative calculation

On Excel for Microsoft 365 for Windows, open File > Options > Formulas. Under Calculation options, select Enable iterative calculation, set Maximum Iterations and Maximum Change if needed, then select OK. Excel can then recalculate circular formulas repeatedly rather than stopping at the circular-reference warning.

On Excel for Mac, open Excel > Preferences (called Settings in some releases), then choose Calculation and enable iterative calculation. The wording or placement can vary slightly by Mac version. Excel for the web does not provide the same desktop calculation-options controls, so configure and check iteration settings in the desktop app when a workbook relies on circular formulas.

To find an existing loop, use Formulas > Formula Auditing > Error Checking and look for Circular References. Excel may also identify a circular-reference cell on the status bar. Follow the listed cell references and inspect the formulas until you find where the chain returns to its starting cell. Enabling iteration should not be the first response to an unexpected warning; first decide whether the loop is intended.

What Excel does during iteration

With iterative calculation enabled, Excel recalculates the formulas in a circular dependency repeatedly. It stops when it reaches the maximum number of iterations or when the change between successive results falls below the maximum-change threshold, whichever happens first. The settings are limits, not a guarantee that every circular formula will settle on a meaningful answer.

Maximum Iterations sets the upper bound on recalculation passes. A higher value can give a slowly converging calculation more opportunity to settle, but it can also mean more work. Maximum Change sets how small the change between results must become before Excel treats the calculation as settled. A smaller threshold can improve numerical precision, but may require more iterations. These controls affect convergence behavior; they do not repair an incorrect formula or prove that a result is mathematically valid.

Excel’s usual calculation mode still matters. In automatic calculation mode, changes to inputs generally trigger recalculation; in manual mode, formulas may not update until you request calculation. If a circular result seems stale, check Formulas > Calculation Options as well as the iteration controls. Workbook calculation behavior can also be affected when a file is opened in another Excel version or spreadsheet program.

A practical circular-reference example

One illustrative use is a commission calculated as a percentage of profit remaining after the commission itself. Suppose B1 contains gross profit of 1000 and B2 contains a commission rate of 10%. With gross profit in B1, the rate in B2, and the commission result in B3, enter this formula in B3:

=(B1-B3)*B2

The formula includes B3 in its own calculation, so it is circular. With iterative calculation enabled, the result approaches 90.91: 10% of 1000 minus the commission. The underlying equation is commission = 10% × (1000 − commission), whose solution is about 90.9091. The precise displayed value depends on number formatting and the chosen iteration threshold. This example is for explaining spreadsheet behavior, not a substitute for checking the terms or accounting treatment that apply to a real commission.

For this simple relationship, a non-circular formula is usually easier to inspect. With gross profit in B1 and the rate in B2, this formula in B3 calculates the same result directly:

=B1*B2/(1+B2)

That version avoids iterative settings and makes the dependency clear. The circular version can still be useful when an established model is designed around feedback between calculations, but it should be documented so another person does not mistake the loop for an accidental reference.

Choosing iteration settings responsibly

There is no single best maximum-iteration or maximum-change setting for every workbook. A simple formula that converges quickly may need little adjustment, while a model with slow feedback may need more passes. Choose settings based on the calculation’s intended precision and behavior, then test the model with representative inputs, including boundary cases such as zero, negative values, and unusually large values where those are valid inputs.

Use these checks when evaluating a circular model:

  • Confirm that each circular dependency is deliberate and that the formulas reflect the intended business or mathematical rule.
  • Compare the result against an independent calculation, a known example, or a non-circular equivalent where possible.
  • Change an input slightly and confirm that results move in a sensible direction rather than oscillating or growing without bound.
  • Keep enough displayed decimal places during review to spot a result that only appears stable because it is rounded.
  • Document why iteration is enabled and what precision the model is expected to achieve.

Iteration can take more time in a large workbook because Excel may repeat calculations through dependent formulas. If recalculation becomes slow, inspect whether the loop is necessary, whether formulas can be simplified, and whether the iteration limit is much higher than the model requires. Avoid choosing a large iteration count merely to suppress a warning.

Common problems and how to troubleshoot them

If Excel still reports a circular reference, confirm that Enable iterative calculation is selected in the desktop calculation settings. Then inspect the cells listed under Formulas > Formula Auditing > Error Checking > Circular References. There may be more than one loop, or a second loop may remain after you fix the first. Trace the references carefully; a formula can participate in a cycle through several intermediate cells.

If the result is zero, blank-looking, or otherwise unexpected, do not assume that the iteration setting is the only cause. Check the formula’s input cells, cell formats, calculation mode, and whether a formula was accidentally overwritten with a constant. A number format can make a small nonzero value appear as zero. Also check whether the formulas actually converge: a loop that adds a fixed positive amount to itself can keep increasing, while other relationships can oscillate between values.

If a workbook behaves differently on another computer, check that the recipient is opening it in a compatible spreadsheet application and that calculation settings and formulas are supported there. Before distributing a workbook that depends on iteration, include a note explaining the circular formulas and test a copy in the environment where it will be used. Do not remove the circular-reference warning by changing settings without verifying the outputs.

When to use a different approach

Use iterative calculation when the circular relationship is part of the model and repeated recalculation is a meaningful way to approximate its result. Avoid it when the loop is accidental, when the result must be easy to audit, or when a direct equation can produce the same answer more transparently. A lookup, a separate input cell, or a rearranged formula may eliminate a loop, depending on the model’s logic.

Another option is to separate assumptions from outputs. For example, if a formula refers to a cell that is intended to be a fixed input, put that input in its own cell and ensure the result formula does not feed back into it. For complex models, document the dependency chain and validate results outside the circular calculation. The goal is not simply to make Excel accept the formula; it is to make the workbook’s logic understandable and reliable.

Frequently Asked Questions

Is it safe to enable circular references in Excel?
It is safe to enable the setting when the circular calculation is intentional and its results have been checked. The setting can also allow an accidental loop to calculate, so it does not replace reviewing formulas or validating outputs.

Why does Excel show a circular reference warning?
Excel found a dependency loop: a formula refers to its own result either directly or through other cells. Use the Circular References list under Formula Auditing to locate the cells, then decide whether to correct the formulas or intentionally use iteration.

What should I set Maximum Iterations and Maximum Change to?
There is no universal setting. Maximum Iterations limits calculation passes, while Maximum Change determines how small the change between results must be before Excel stops. Start with appropriate modest limits, test the model’s convergence and precision, and adjust only when the model requires it.

Can I use circular references in Excel for the web?
The web app does not expose the same iterative-calculation controls as desktop Excel. If a workbook depends on circular formulas, configure and verify the calculation in Excel for Windows or Mac, then check the result in the web app before relying on it.

How do I remove a circular reference?
Use the Circular References list to identify the loop, then edit the formulas so the dependency no longer leads back to the starting cell. If the relationship can be rearranged into a direct formula, that is often easier to audit than enabling iteration.

Related

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.