Change when and how formulas are recalculated

By default, Microsoft Excel automatically recalculates formulas when the cells that the formula depends on have changed.

ShowRecalculate a worksheet or workbook manually using keyboard shortcuts

F9     Recalculates formulas that have changed since the last calculation, and formulas dependent on them, in all open workbooks. If a workbook is set for automatic recalculation, you do not need to press F9 for recalculation.

SHIFT+F9     Recalculates formulas that have changed since the last calculation, and formulas dependent on them, in the active worksheet.

CTRL+ALT+F9     Recalculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.

CTRL+SHIFT+ALT+F9     Rechecks dependent formulas, and then recalculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.

ShowChange when a worksheet or workbook recalculates

  1. On the Tools menu, click Options, and then click the Calculation tab.
  2. Do one of the following:
    • To recalculate all dependent formulas every time you make a change to a value, formula, or name, click Automatic. This is the default calculation setting.
    • To recalculate all dependent formulas—except data tables—every time you make a change to a value, formula, or name, click Automatic except tables.
    • To turn off automatic recalculation and recalculate open workbooks only when you explicitly do so (by clicking Calc Now (F9) on the Calculation tab), click Manual.

 Note   When you click Manual, Excel automatically selects the Recalculate before save check box. If saving a workbook takes a long time, clearing Recalculate before save may improve the save time.

  • To manually recalculate all open worksheets, including data tables, and update all open chart sheets, click Calc Now (F9).
  • To manually recalculate the active worksheet and any charts and chart sheets linked to this worksheet, click Calc Sheet.

Changing any of the options affects all open workbooks.

 Note   If a worksheet contains a formula that is linked to a worksheet that has not been recalculated and you update that link, Excel displays a message stating that the source worksheet is not completely recalculated. To update the link with the current value stored on the source worksheet, even though the value might not be correct, click OK. To cancel updating the link and use the previous value obtained from the source worksheet, click Cancel.

ShowChange the number of times Microsoft Excel iterates a formula

Iteration is the repeated recalculation of a worksheet until a specific numeric condition is met. For example, Goal Seek and Solver continually recalculate formulas using different values one after another, which may take a long time. Circular references can iterate indefinitely.

  1. On the Tools menu, click Options, and then click the Calculation tab.
  2. Select the Iteration check box.
  3. To set the maximum number of times Microsoft Excel will recalculate, type the number of iterations in the Maximum iterations box. The higher the number of iterations, the more time Excel will need to recalculate a worksheet.
  4. To set the maximum amount of change you will accept between recalculation results, type the amount in the Maximum change box. The smaller the number, the more accurate the result and the more time Excel needs to recalculate a worksheet.
 
 
Applies to:
Excel 2003