Skip to content
Formula Audit XL

Enable Iterative Calculation in Excel

Updated 2026-11-12

Quick answer

To enable iterative calculation in Excel for Windows, go to File, Options, Formulas, and tick Enable iterative calculation under Calculation options. Excel sets Maximum Iterations to 100 and Maximum Change to 0.001 by default. On a Mac, go to Excel, Preferences, Calculation. The setting applies to every open workbook, and Excel takes it from the first workbook opened in the session. Turn it on only for a loop you built on purpose.

To enable iterative calculation in Excel for Windows, go to File, Options, Formulas and tick Enable iterative calculation. Excel then repeats the calculation of a circular formula until the result settles. The defaults are 100 maximum iterations and a maximum change of 0.001. Turn the setting on only when the circular reference is deliberate. If you are not sure the loop is intended, find the circular reference first.

What iterative calculation does

A circular reference is a formula that depends on its own result. With iteration off, Excel shows a warning and usually returns 0. With iteration on, Excel calculates the loop again and again. It stops when one of two limits is reached:

  • Maximum Iterations: the number of passes. The default is 100.
  • Maximum Change: the largest change between two passes that Excel still treats as settled. The default is 0.001.

A higher Maximum Iterations or a smaller Maximum Change gives a more precise result, but the workbook takes longer to calculate.

How to enable iterative calculation in Excel for Windows

  1. Click File, then Options.
  2. Click Formulas in the left pane.
  3. Under Calculation options, tick Enable iterative calculation.
  4. Set Maximum Iterations (default 100) and Maximum Change (default 0.001).
  5. Click OK.
  6. Save the workbook so the setting is stored with it.

The steps are the same in Excel 2016, 2019, 2021 and Microsoft 365 for Windows.

How to enable iterative calculation in Excel for Mac

  1. Click Excel in the menu bar, then Preferences. Newer versions name this menu Settings.
  2. Click Calculation.
  3. Tick Use iterative calculation.
  4. Enter the maximum iterations and the maximum change.
  5. Close the dialog and save the workbook.

Excel on the web

You cannot change the iteration setting in Excel on the web. Click Open in Desktop App, change the setting in Excel for Windows or Mac, and save the file.

The setting is application-wide

Iterative calculation is not a property of one sheet or one workbook while Excel runs. It applies to every workbook open in that Excel session.

  • Excel takes the setting from the first workbook opened in the session.
  • Workbooks opened after it follow the same setting, whatever was saved in them.
  • When you save a workbook, Excel stores the setting that is active at that moment.

This explains a common problem. A model saved with iteration on still shows a circular reference warning when another workbook with iteration off opened first. Open the circular model first, or turn iteration on again after you open it. It also means you can save iteration on into an unrelated file without noticing.

When to use iterative calculation

Use it when a model is circular on purpose and the loop converges. Common examples in financial models:

  • Interest on the average debt balance. Interest reduces cash flow, cash flow sets the closing debt, and the closing debt sets the average balance that drives interest.
  • Cash sweep. The repayment depends on cash available after interest, and interest depends on the repayment.
  • Fees on the amount raised. The fee is a percentage of the funding, and the funding has to cover the fee.

Put a switch cell on the inputs sheet, for example Circularity on = 1. Multiply the circular link by it, so you can set it to 0 and break the loop if the model returns an error that keeps feeding back into itself.

Risks of iterative calculation

  • It hides accidental circular references. With iteration on, Excel stops warning about every loop, not only the one you built. A total that includes its own cell returns a number and no warning.
  • Results that do not converge. Some loops never settle. The values grow or swing between passes, and Excel stops at the iteration limit with a result that is wrong.
  • Errors stick. If an error such as #DIV/0! enters the loop, it can keep feeding back into the loop after you fix its cause. Use the switch cell to break and reset the loop.
  • Different results on different machines. Because the setting comes from the first workbook opened, the same file can calculate on one computer and warn on another.

Check for accidental loops before you enable it

Before you turn iteration on, list every circular reference in the workbook and confirm that each one is deliberate.

  • The free Excel Circular Reference Finder reads an .xlsx, .xlsm or .xls file in your browser and lists the circular references it finds across all sheets. The file stays on your computer.
  • In Excel, the Formula Audit XL Audit Checks suite includes a Circular References check (Ctrl+Shift+C). It lists the circular references it finds, with the cells involved, and a click jumps to each one.

Formula Audit XL is available as an Excel add-in for Windows and as an AppSource task pane for Windows, Mac and Excel on the web.

Free tool: Excel Circular Reference Finder

The faster way

Run this check across your entire model with Formula Audit XL.

Audit Checks

Frequently asked questions

How do I enable iterative calculation in Excel?

In Excel for Windows, go to File, Options, Formulas. Under Calculation options, tick Enable iterative calculation, set Maximum Iterations and Maximum Change, and click OK. Excel then repeats the calculation of a circular formula until the result settles or the iteration limit is reached.

How do I enable iterative calculation in Excel for Mac?

Go to Excel, Preferences, Calculation (newer versions name the menu Settings instead of Preferences). Tick Use iterative calculation, enter the maximum iterations and the maximum change, and close the dialog.

What are the default Maximum Iterations and Maximum Change in Excel?

Maximum Iterations defaults to 100 and Maximum Change defaults to 0.001. Excel stops when either limit is reached: after 100 passes, or when no value changes by more than 0.001 between two passes.

Is iterative calculation saved with the workbook?

Yes, but Excel applies it to the whole application. The setting of the first workbook opened in a session applies to every workbook opened after it. When you save a file, Excel stores the setting that is active at that moment.

Why does my circular reference warning come back after I enabled iteration?

Another workbook with iteration off was probably opened first in the session, so Excel used its setting. Close all workbooks, open the circular model first, or turn iteration on again in File, Options, Formulas.

Should I enable iterative calculation to get rid of a circular reference warning?

Only when the loop is deliberate, such as interest on an average debt balance. If the loop is a mistake, iteration hides it and the model returns a wrong number without a warning. Find and fix the loop instead.

Audit your whole model in minutes

Formula Audit XL runs this check and the full audit across every sheet at once.

For Microsoft Excel on Windows, Mac and the web · No data leaves your machine