Circular Reference Settings in Excel: How They Work

How-to basics · Updated November 18, 2026

In Excel for Microsoft 365 on Windows, turn on circular-reference calculations at File > Options > Formulas by selecting Enable iterative calculation; then set Maximum Iterations and Maximum Change. Use this only when a formula is deliberately meant to refer back to itself, because iteration can make results approximate or unstable, and most accidental circular references should be corrected instead.

What a circular reference means

A formula has a circular reference when its calculation depends on its own result. The dependency can be direct, as when a cell refers to itself, or indirect, when a chain of formulas eventually leads back to the starting cell. For example, if A1 contains a value, this formula entered in A1 refers directly to A1:

With the formula placed in A1, this simple example refers to its own cell: =A1+1

An indirect loop might be A1 depending on B1, B1 depending on C1, and C1 depending on A1. The issue is the dependency path, not the number of cells involved. Excel normally expects to calculate formulas from inputs toward results; a circular chain has no ordinary first step because its result is also one of its inputs.

When Excel encounters a circular reference, it may show a warning and identify the cell. Without iterative calculation enabled, the result may not calculate as intended. A value displayed in a cell is not proof that the formula is correct: it might be a leftover value, a zero, or a result from a workbook with iteration enabled. Inspect the formula and the workbook’s calculation settings before relying on it.

How to enable iterative calculation

On Windows, open the workbook and follow these steps:

  1. Select File > Options.
  2. Choose Formulas in the Excel Options window.
  3. Under Calculation options, select Enable iterative calculation.
  4. Set Maximum Iterations and Maximum Change, then select OK.

On Excel for Mac, open Excel > Settings (called Preferences in some releases) > Calculation, then enable iterative calculation and adjust the iteration controls. The wording and layout can vary slightly between builds. Excel for the web does not provide the same desktop settings controls, so configure and verify an iterative workbook in the desktop app.

The iteration options are saved with workbook calculation properties, but a recipient’s Excel version and calculation environment can affect what they see. If the workbook will be shared, save it after setting the options, reopen it in the intended desktop version, and verify both the setting and the result. Do not assume that a circular formula will behave consistently merely because a cached value appears in a preview or web view.

What Maximum Iterations and Maximum Change do

Excel repeats the circular calculation until it reaches one of two stopping conditions: it has performed the allowed number of iterations, or the change between successive results is smaller than the Maximum Change threshold. Whichever condition comes first stops the calculation. Maximum Iterations limits how many rounds Excel performs; Maximum Change sets the tolerance for treating a result as settled.

Excel commonly starts with Maximum Iterations set to 100 and Maximum Change set to 0.001, though a workbook may have different values. A larger iteration limit gives a slowly converging formula more opportunities to settle, but it can also take longer and does not make a formula logically sound. A smaller Maximum Change demands closer numerical stability, but may require more rounds. Neither control guarantees the correct business answer: they control the calculation process, not the meaning of the model.

Choose values based on the formula and the precision the workbook needs. A threshold of 0.001 means Excel can stop when successive results differ by less than that amount; it does not mean every displayed value is accurate to three decimal places. Results may also depend on starting values and formula behavior. Test the workbook with known inputs and compare its output with an independent calculation where possible.

A small example of an intentional circular formula

Suppose A1 contains the target value 100, and A2 is designed to move halfway from its current value toward that target on each pass. With the input in A1 and the formula in A2, the formula is:

With 100 in A1 and the formula entered in A2, this circular calculation repeatedly moves A2 halfway toward 100: =(A1+A2)/2

A2 refers to itself, so it is circular. Starting from zero, successive results move toward 100: 50, 75, 87.5, and so on. With a sufficiently high iteration limit and a suitably small change threshold, the value approaches 100. It does not become exactly 100 after a finite number of these halvings, so the stopping threshold matters. This is a mathematical illustration, not a recommendation to make ordinary spreadsheet calculations circular.

Contrast that with a formula that does not settle. With 100 in A1 and the formula in A2, this example alternates between values rather than converging:

With 100 in A1 and this formula in A2, each pass switches between two results: =A1-A2

Depending on the starting value, repeated recalculation can bounce between values instead of approaching one stable result. Increasing Maximum Iterations will not solve that problem; it merely allows more oscillations before Excel stops. When iteration reaches its limit, inspect the result rather than treating it as a dependable answer.

How to find and trace circular references

If Excel warns about a circular reference, start by checking the cell named in the warning or on the status bar. In Windows desktop Excel, use Formulas > Error Checking > Circular References to see listed circular-reference cells, then select an address to jump to it. The status bar can also identify a circular reference. If more than one loop exists, fixing one may reveal another, so check the menu again after each correction.

To trace an indirect loop, inspect the formulas feeding the flagged cell and the formulas those cells depend on. The Formula Auditing tools can help: select a cell and use Formulas > Trace Precedents to show cells that feed it, or Trace Dependents to show cells that use it. Follow the arrows through the chain until you find the path back to the original cell. Remove the arrows with Formulas > Remove Arrows when finished.

Common causes include a SUM range that accidentally includes its own total cell, a formula pasted into the range it totals, or a link between sheets that loops back. For example, if a total is in B10, check that the SUM range ends above B10 rather than including B10 itself. Also check named ranges and formulas on other worksheets: the visible formula may look harmless while a referenced cell creates the return path.

When iterative calculation is useful—and when it is risky

Iteration can be appropriate when a model intentionally defines a value in terms of its own previous or current estimate and the calculation is known to converge. Some specialized planning or engineering models use this approach. In every case, document why the loop exists, what stopping tolerance is acceptable, and how the output was checked. A colleague opening the workbook should not have to guess whether the circular reference is intentional.

For routine workbooks, a circular reference is more often a formula mistake. Enabling iteration to suppress a warning can hide a bad range, an unintended dependency, or an incorrect model. It may also produce values that change when inputs, formulas, iteration limits, or starting values change. Treat unexpected circular references as errors to investigate, not as a prompt to switch on iteration.

Iteration is not a substitute for Excel’s Goal Seek or Solver. Goal Seek is useful when you want Excel to change one input cell to make a formula reach a target value; it does not generally turn an unintended circular formula into a well-designed model. Solver can handle more involved optimization problems when available and configured, but it is a distinct tool with its own constraints and assumptions.

Common problems and practical checks

  • The warning returns after a fix: There may be another circular loop, or a dependent formula still points back to the original cell. Recheck Circular References after each change.
  • The value changes unexpectedly: Review Maximum Iterations, Maximum Change, and the formulas in the whole dependency chain. Test with simpler inputs to see whether values converge.
  • The result seems frozen: Confirm that Enable iterative calculation is on for the workbook and that Excel is recalculating. Also check whether calculation is set to Manual under Formulas > Calculation Options.
  • A workbook behaves differently on another computer: Confirm the recipient’s Excel version, iteration settings, calculation mode, and whether they opened it in desktop Excel. Ask them to recalculate and validate the result.
  • A total formula causes the loop: Move the total outside the range or adjust the range so it excludes the result cell. This is usually safer than enabling iteration.

Before distributing a workbook with deliberate iteration, include a note explaining the loop and the selected limits, then test representative cases, including boundary inputs. If the result is consequential, provide an independent check or a non-circular calculation alongside it. A compact test sheet can make it easier for future editors to verify that the model still converges after changes.

Alternatives to circular formulas

Often the clearest repair is to separate inputs, intermediate calculations, and outputs into different cells. A helper cell can hold a value that otherwise gets mixed into a total, while a carefully defined range can prevent a summary formula from including itself. If the circular relationship is algebraically solvable, rewrite it as a direct formula; this removes repeated calculation and makes the dependency easier to audit.

For a model that genuinely needs repeated estimates, consider making each step explicit in a sequence of rows or columns. That exposes how values change from one pass to the next and can make convergence or oscillation visible. The tradeoff is a larger sheet and a chosen number of steps, but the logic is often easier to explain than a hidden iterative loop.

Frequently Asked Questions

Should I enable iterative calculation in Excel?
Only if the circular reference is intentional and the formula is designed to converge. If the loop appeared unexpectedly, trace and fix it instead of enabling iteration to conceal the warning.

What are the default iteration settings in Excel?
Excel commonly uses 100 maximum iterations and a maximum change of 0.001, but workbook settings can differ. Check File > Options > Formulas on Windows or Excel > Settings/Preferences > Calculation on Mac.

Why does Excel still show a circular reference after I fix one?
The workbook may contain another direct or indirect loop. Use Formulas > Error Checking > Circular References again and trace the dependencies of each listed cell.

Does Maximum Change control the number of decimal places?
No. It is the tolerance for change between calculation passes, not a display-format setting or a guarantee of accuracy to a particular number of decimal places.

Can I use circular references in Excel for the web?
For reliable setup and troubleshooting, open the workbook in desktop Excel, where the iterative-calculation controls are available. A web view should not be treated as proof that an iterative workbook has been configured or recalculated as intended.

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.