How Excel Determines Calculation and Iteration Settings

You should understand that all open workbooks use the same calculation and iteration settings. For example, if you have two workbooks open, you cannot have one of them set to automatic calculation and the other set to manual calculation. Although you can save a workbook with particular settings (for example, manual calculation with no iterations), those settings can change if you open another workbook.

Excel follows these general rules to determine which calculation and iteration settings to use:

• The first workbook opened uses the calculation mode saved with that workbook. If you open other workbooks, they use the same calculation mode.

  For example, suppose you have two workbooks: Book1 and Book2. Book1 has its Iteration setting turned off (the default setting), and Book2 (which uses intentional circular references) has its Iteration setting turned on. If you open Book1 and then Book2, both workbooks will have the iteration setting turned off. If you open Book2 and then Book1, both workbooks will have their iteration setting turned on.

• Changing the calculation mode for one workbook changes the mode for all workbooks.

  If you have both Book1 and Book2 open, changing the calculation mode or Iteration setting of either workbook affects both workbooks.

• All worksheets in a workbook use the same mode of calculation.

• If you have all workbooks closed and you create a new workbook, the new workbook uses the same calculation mode as the last closed workbook. The exception is if you create the workbook from a template, the workbook uses the calculation mode specified in the template.

• If the mode of calculation in a workbook changes and you save the file, the current mode of calculation saves with the workbook.

Bottom line? When you open a workbook that uses iteration, there is no guarantee that the setting saved with your workbook will be the setting that is in effect when you open the workbook.

Caution 

When the Enable Iterative Calculation setting is in effect, Excel will never display the Circular References warning dialog box and will not display the Circular References message in the status bar. Therefore, you may create an unintentional circular reference and not even know about it.