How to Turn On Circular Reference Iteration in Excel

In Excel for Microsoft 365 on Windows, turn on circular-reference calculation by going to File > Options > Formulas, checking Enable iterative calculation, and choosing iteration limits. This lets Excel recalculate formulas that refer to their own cell, directly or through other cells. Before enabling it, confirm the circular reference is intentional: iteration can calculate a deliberate model, but it will not fix a mistaken formula.

A circular reference is a calculation loop. Normally, Excel warns about the loop because it cannot produce a straightforward result in one pass. Iterative calculation allows repeated passes, stopping when the result changes by less than a chosen amount or when it reaches the maximum number of passes. The instructions below use a small commission example to show the setting and help you check that it is working.

What You’ll Need

Excel for Microsoft 365 desktop on Windows. The workbook should be open and saved somewhere you can find it.
A formula or model that genuinely needs a circular reference, plus the inputs it depends on. If you only see a circular-reference warning and do not intend to create a loop, you probably need to correct a cell reference instead.
A moment to check the results after enabling iteration. Iterative calculation can affect how circular formulas calculate, so do not rely on a result until it behaves as expected.

Step-by-Step

Decide whether the loop is intentional. Identify the formula that refers back to itself, either directly or through another formula. A direct loop occurs when a cell’s formula includes that same cell. An indirect loop occurs when, for example, A1 depends on B1 and B1 depends on A1. Circular formulas are sometimes used in financial models where a value depends on a total that includes that value. If you cannot explain why the loop is needed, stop and inspect the references before changing Excel’s settings.
Open Excel’s calculation settings. In Windows Excel, select File > Options, then choose Formulas in the Excel Options window. Look for the Calculation options section. The iterative setting is in this section; it is not a button on the worksheet and does not remove or rewrite the formula that created the circular reference.
Enable iterative calculation. Select the checkbox named Enable iterative calculation. Once enabled, Excel can recalculate circular formulas repeatedly rather than stopping at the first loop. Leave the other settings at their current values for an initial check unless your model has a reason to require different limits. Select OK to close Excel Options and return to the workbook.
Review Maximum Iterations and Maximum Change. Maximum Iterations is the upper limit on recalculation passes. Maximum Change is the tolerance: Excel can stop when the difference between successive results is smaller than this value. Common starting values are 100 iterations and 0.001 maximum change. More iterations may help a slowly converging model; a smaller maximum change asks for a closer result, but may require more passes. These controls work together, so avoid increasing the iteration limit without checking whether the result is actually converging.
Try a simple, controlled example. In a blank test sheet, enter 10000 in B2 for sales before commission and 10% in B3 for the commission rate. With those inputs in B2 and B3, enter the following formula in B4 for commission, where the commission is calculated on sales including the commission itself: =(B2+B4)*B3. This is a direct circular reference because B4 appears in its own formula. With iterative calculation enabled, B4 should settle near 1111.11. The arithmetic is 10% of 10000 plus the commission; solving c = 0.1 × (10000 + c) gives c = 1111.11 recurring. Format B4 as currency if you want to display two decimal places.
Check the workbook result and save it. Confirm that the test or intended model produces a stable, sensible result rather than an error, an unexpected zero, or a value that keeps changing. Save the workbook after checking it. If you use several workbooks at once, verify their calculations too: calculation behavior can interact with Excel’s calculation settings, and an option that helps one model may not be suitable for another.
Use the equivalent setting on Mac if needed. Excel for Mac places calculation preferences under Excel > Settings > Calculation; in some releases the menu is labelled Preferences instead of Settings. Find the iterative calculation option, enable it, and review the iteration limit and change tolerance. The labels or layout can vary slightly by release. For Excel for the web, use desktop Excel to configure the iteration settings; if a workbook’s circular formulas do not calculate as expected in the browser, open it in the desktop app and verify the settings there.

Common Mistakes

Turning it on to silence a warning without checking the formula. Enabling iteration does not identify whether the loop is intentional. A typo such as referring to the total cell instead of an input cell can create a circular reference accidentally. Trace the references first and correct unintended loops rather than allowing them to calculate.

Assuming any circular formula will converge. Iteration is repeated calculation, not a guarantee of a useful answer. A formula can oscillate between values, grow without limit, or converge too slowly. For example, if each pass increases the result rather than bringing it closer to a stable value, raising Maximum Iterations may only make Excel spend longer calculating.

Setting Maximum Change too loosely. A large tolerance can make Excel stop while the result is still too imprecise for the task. A smaller tolerance can improve precision, but can also increase calculation time. Choose a tolerance that makes sense for the units and accuracy of the model, then check the displayed result against an independent calculation when possible.

Confusing a displayed value with the underlying result. Formatting a cell to show two decimal places only changes how the result looks. It does not change the underlying precision or the Maximum Change setting. If a financial model needs a particular rounding rule, apply an appropriate rounding method deliberately rather than relying only on cell formatting.

Tips

Test a new circular formula in a copy of the workbook or on a separate sheet before using it in a live model. Enter known inputs, write down the result you expect, and compare it with Excel’s calculated value. For the commission example, the fixed-point result is approximately 1111.11, so a substantially different value is a reason to check the inputs, references, and iteration settings.

Keep the loop as simple as possible. When a circular reference involves many sheets or several linked formulas, it can be difficult to tell which calculation is driving a result. Use clear labels for inputs and outputs, and document why the loop is necessary. If you can replace the circular design with a direct calculation that is easier to audit, that is often safer for a shared workbook.

Change one calculation setting at a time and observe the effect. If the value is converging but not reaching your chosen tolerance, a higher iteration limit may help. If it reaches the limit while still changing substantially, investigate the model instead of continually raising the limit. A result that has stopped changing because Excel hit its cap is not proof that the model has converged.

Troubleshooting

The circular-reference warning still appears. First confirm that Enable iterative calculation is checked in File > Options > Formulas. Then look for other circular references: fixing one loop does not necessarily remove another. In Windows Excel, check Formulas > Error Checking > Circular References for listed cells. Selecting a listed reference can help you inspect the cell, though an indirect loop may require tracing several formulas to find the full path.

The result is zero, blank, or not what you expected. Check that the formula contains the intended cell references and that all input cells contain usable values. Confirm that iterative calculation is enabled in the Excel desktop app, not just that the workbook has been opened in a browser. Also check whether calculation is set to automatic: select Formulas > Calculation Options > Automatic if the workbook is not recalculating as inputs change. Re-entering a test input or recalculating can help reveal whether the value updates.

The value changes each time or looks unstable. Watch the result across recalculations and inspect the formulas in the loop. A change that alternates between two values suggests oscillation; a steadily increasing or decreasing value may indicate divergence. Do not assume that increasing Maximum Iterations will resolve either behavior. Revisit the model’s logic, signs, rates, and references, and consider whether the circular design can be replaced.

Excel becomes slow or the result is too rough. A very high iteration limit can make a workbook slower, especially if many formulas depend on the loop. Reduce unnecessary dependencies, test with a smaller workbook, and select a tolerance appropriate to the model. If the value stops because it reaches Maximum Iterations, the model may need a better calculation approach rather than a still higher cap.

The setting seems unavailable or does not work in the browser. Open the file in Excel for Microsoft 365 desktop and use the Windows or Mac calculation settings described above. If the workbook is shared, check that it is saved after configuring it and ask collaborators to verify the result in their Excel environment. Do not treat an unchanged browser display as confirmation that the circular formula has converged.

Frequently Asked Questions

Does enabling iterative calculation remove circular references?
No. The formula still contains a loop. Enabling iteration tells Excel how to attempt to calculate that loop; it does not edit formulas or correct accidental references.

What is the difference between Maximum Iterations and Maximum Change?
Maximum Iterations limits how many recalculation passes Excel can make. Maximum Change sets how small the difference between successive results must become before Excel can stop because the result is stable enough. Excel can also stop when it reaches the iteration limit.

Can I turn on iteration for only one cell?
The option is a calculation setting, not a per-cell switch. Treat it as relevant to the workbook’s calculation behavior and check other circular formulas after changing it. If other workbooks are open, verify their results as well.

Is a circular reference always wrong?
No. Some models intentionally use a circular relationship and are designed to calculate by iteration. But an accidental loop can hide a formula error, so confirm the reason for the circularity and check that the result converges to a sensible value.

Will the circular formula work in Excel for the web?
For dependable setup and troubleshooting, configure iterative calculation in desktop Excel. If the workbook’s result differs in the browser, open it in the desktop app and check the calculation settings and formulas rather than assuming the model is correct.

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.