- Mar 26, 2008
Controlling Worksheet Calculation
Excel always calculates a formula when you confirm its entry, and the program normally recalculates existing formulas automatically whenever their data changes. This behavior is fine for small worksheets, but it can slow you down if you have a complex model that takes several seconds or even several minutes to recalculate. To turn off this automatic recalculation, Excel 2007 gives you two ways to get started:
- Choose Formulas, Calculation Options.
- Choose Office, Excel Options and then click Formulas.
Either way, you're presented with three calculation options:
- Automatic—This is the default calculation mode, and it means that Excel recalculates formulas as soon as you enter them and as soon as the data for a formula changes.
- Automatic Except for Data Tables—In this calculation mode, Excel recalculates all formulas automatically, except for those associated with data tables. This is a good choice if your worksheet includes one or more massive data tables that are slowing down the recalculation.
To learn how to set up data tables, see "Using What-If Analysis," p. 361.
- Manual—Choose this mode to force Excel not to recalculate any formulas until either you manually recalculate or until you save the workbook. If you're in the Excel Options dialog box, you can tell Excel not to recalculate when you save the workbook by clearing the Recalculate Workbook Before Saving check box.
With manual calculation turned on, you see Calculate in the status bar whenever your worksheet data changes and your formula results need to be updated. When you want to recalculate, first display the Formulas tab. In the Calculation group, you have two choices:
- Click Calculate Now (or press F9) to recalculate every open worksheet.
Click Calculate Sheet (or press Shift+F9) to recalculate only the active worksheet.
If you want to recalculate only part of your worksheet while manual calculation is turned on, you have two options:
- To recalculate a single formula, select the cell containing the formula, activate the formula bar, and then confirm the cell (by pressing Enter or clicking the Enter button).
- To recalculate a range, select the range; choose Home, Find & Select, Replace (or press Ctrl+H); and enter an equals sign (=) in both the Find What and Replace With boxes. Click Replace All. Excel "replaces" the equals sign in each formula with another equals sign. This doesn't change anything, but it forces Excel to recalculate each formula.