Enable Circular References in Excel: Step-by-Step

To enable circular references in Excel for Microsoft 365 on Windows, open the workbook, select File > Options > Formulas, check Enable iterative calculation, and select OK. This lets Excel recalculate formulas that refer to themselves, either directly or through other cells. Before turning it on, make sure the circular formula is intentional: iteration can make a designed calculation work, but it can also hide a mistake.

A circular reference happens when a formula depends on its own result. For example, a formula in A1 that refers to A1 is a direct circular reference. A formula in A1 that relies on B1, while B1 relies on A1, creates an indirect one. Excel normally flags these formulas; iterative calculation lets it repeat calculations until a stopping limit is reached.

What You’ll Need

A copy of the workbook, or permission to edit it. Save a backup before changing formulas or calculation settings.
Excel desktop for Windows, ideally Microsoft 365. The steps below use the Windows desktop interface.
A specific reason to use a circular formula, such as a model that needs to calculate interest on a balance that includes that interest.
A starting value or a clear rule for how the formula should converge. Iterative calculation does not make an unstable or incorrect formula mathematically sound.

Step-by-Step

Open the workbook that needs iteration. Select the workbook in Excel before changing the setting. If you have several workbooks open, check the active workbook name so you do not adjust a different file by mistake. Save a backup first, particularly if the file contains financial, reporting, or operational calculations.
Open Excel’s calculation settings. In Windows, select File > Options, then choose Formulas in the Excel Options window. Find the Calculation options section. The setting you need is called Enable iterative calculation. It is not the same as choosing Automatic or Manual calculation elsewhere in the same section.
Turn on iterative calculation. Check Enable iterative calculation. Excel will then allow circular formulas in the current workbook to calculate repeatedly instead of stopping immediately with a circular-reference warning. The setting is saved with the workbook, so save the file after enabling it if you want the change to travel with the workbook.
Choose a maximum number of iterations. Maximum Iterations sets how many times Excel can repeat calculations before it stops. The default is commonly 100. A larger number can help a slowly converging calculation, but it may take longer and does not fix a formula that never settles. For a simple test or a model that converges quickly, the default is usually a reasonable starting point.
Set the maximum change. Maximum Change is the tolerance at which Excel can stop iterating because the values are no longer changing by more than the specified amount. The common default is 0.001. A smaller tolerance may produce a more precise result but can require more calculations; a larger tolerance can stop sooner while leaving a less precise result. Choose a value that fits the units and accuracy your model needs.
Confirm the settings. Select OK to close Excel Options. If Excel displays a circular-reference notice, that means it has detected a circular formula; it does not by itself confirm the formula is designed correctly. Save the workbook, then check the result in the relevant cells.
Try a controlled example if you want to verify the setting. Use a blank worksheet and put the test formula in A1. With no other data in the sheet and A1 initially blank, enter =IF(A1<10,A1+1,A1). With iterative calculation enabled and enough iterations, the result is 10: the formula adds 1 until A1 reaches 10, then returns that value. Clear the test cell afterward if you do not need it.
Inspect the formula and its result in the real model. Check the cells involved, confirm that each reference is intentional, and compare the output with an independent calculation or known example. If the result changes unexpectedly, revisit the formula logic and iteration limits rather than simply increasing the maximum iterations.

To enable the setting on a Mac, open Excel’s preferences or settings and look for Calculation; in many versions the path is Excel > Preferences > Calculation. Turn on Use iterative calculation, then set the iteration count and maximum change if those controls are available. Menu names can vary by Mac version. Excel for the web does not provide the same desktop File > Options > Formulas controls, so open the workbook in the desktop app to change the iterative-calculation setting.

Common Mistakes

Enabling iteration before identifying the circular formula. A circular reference may be an accidental typo, such as a SUM range that includes its own result cell. Find the reference and decide whether it is meant to loop before allowing Excel to iterate.
Assuming the setting repairs the formula. Iteration changes how Excel calculates a circular formula; it does not determine whether the business logic is correct. A formula can converge to a number that is still wrong for the task.
Setting the iteration limit too low. A calculation that needs more cycles than the limit may stop before it has converged. Check whether the result is stable when you reasonably increase the limit, and verify it independently.
Using an unsuitable maximum change. A tolerance that is too large may stop calculation before the result is accurate enough. A tiny tolerance can also make a calculation slow or prevent it from stopping by tolerance. Consider whether the cells contain dollars, percentages, whole numbers, or very small quantities when choosing a value.
Confusing iteration with calculation mode. Automatic and Manual determine when Excel recalculates; iterative calculation determines whether it can repeat a circular calculation. If calculation is Manual, changes may not appear until you recalculate.
Forgetting that a workbook contains a circular formula. Someone opening the file may not understand why the calculation behaves differently from a workbook without iteration. Document the purpose and the chosen limits, especially in a shared model.

Tips

Keep a short note beside the model or in its documentation explaining which cells form the loop, why the loop is needed, and what the iteration limits mean.
Use the smallest sensible iteration count and a tolerance that meets the model’s accuracy requirements. Very high limits are not a substitute for checking whether values actually converge.
Test changes in a copy of the workbook. Compare outputs before and after a formula edit, and use a known case to confirm the result makes sense.
For financial models, agree on rounding and tolerance with the people who use the workbook. A small difference can matter when values are aggregated or reported to a required precision.
Review the Calculation options setting when a workbook is passed to another person or opened on another computer. Keep the workbook saved in a format that preserves Excel formulas and settings, such as .xlsx or .xlsm when macros are needed.

Troubleshooting

Excel still reports a circular reference. That message can remain because the formula is still circular; enabling iteration allows it to calculate rather than removing the circular structure. Use Formulas > Error Checking > Circular References to inspect the cells Excel lists, then follow the references through the workbook. A circular loop can involve several sheets, so check references beyond the first cell shown.

The output is blank, zero, or not what you expected. Confirm that Enable iterative calculation is checked for the workbook you are editing, and verify that the formula refers to the intended cells. Check for blank or stale starting values and look for other formulas in the loop that may return text, errors, or values that keep changing. Recalculate after correcting the inputs. If calculation mode is Manual, use Formulas > Calculate Now, or switch the workbook to Automatic if that is appropriate for your file.

The result changes each time Excel recalculates. The formula may not converge, or it may depend on a volatile input or another changing calculation. Raising Maximum Iterations can help only when the values are moving toward a stable result. If they keep oscillating or growing, redesign the formula or set a sensible starting point instead of relying on a larger limit.

The workbook is slow. Circular calculations can require many passes, particularly in a large workbook with dependencies across many sheets. Reduce unnecessary formulas in the loop, avoid setting an excessive iteration count, and check whether the calculation can be restructured to avoid circularity. Test any restructuring against expected results.

You cannot find the setting. In Windows desktop Excel, use File > Options > Formulas and look under Calculation options. On Mac, look under Excel > Preferences or Excel > Settings, then Calculation; the exact wording depends on the release. If you are using Excel for the web, open the file in desktop Excel to access the iterative-calculation controls.

Frequently Asked Questions

What does enabling circular references do?
It lets Excel repeatedly recalculate formulas that depend on their own results. Excel stops when it reaches the maximum iteration count or the calculation meets the maximum-change tolerance.

Does enabling iterative calculation remove the circular reference?
No. The formula still contains a circular dependency. The setting permits Excel to calculate it iteratively; it does not rewrite the formula or remove the dependency.

Can I enable circular references for just one formula?
The setting is not a switch attached to a single formula. It enables iterative calculation for the workbook, so review all circular formulas in that file and make sure each one is intentional.

Will the setting remain enabled after I close Excel?
When you save the workbook, its iterative-calculation setting is saved with it. Save after changing the option, and check the setting if the workbook is copied, converted, or opened in another spreadsheet program.

Why does Excel show a circular-reference warning after I enable the option?
The warning identifies a circular dependency, which can still be present even when iteration is allowed. Use Formulas > Error Checking > Circular References to locate it and confirm that the loop is intended.

Should I use a high maximum iteration count?
Not automatically. Start with a suitable count and tolerance, then check whether the output stabilizes and matches an independent result. If it does not converge, investigate the formula logic rather than only increasing the limit.

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.