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, follow these steps:
Choose Tools, Options. The Options dialog box appears.
Select the Calculation tab to display the Calculation options (see Figure 3.2).
To disable automatic recalculation, choose Manual. If you'd prefer to leave automatic calculation on except for data tables, select the Automatic Except Tables option instead.
Figure 3.2 Select the Calculation tab in the Options dialog box to control worksheet calculations.
To learn how to set up data tables, see "Using What-If Analysis," p. 315.
-
If you chose Manual, you also can tell Excel not to recalculate before saving the worksheet by clearing the Recalculate Before Save check box.
Click OK.
With manual calculation turned on, you'll see a Calculate message appear in the status bar whenever your worksheet data changes and your formula results need to be updated. When you want to recalculate, choose Tools, Options; select the Calculation tab; and then click one of the following buttons:
Click Calc Now to recalculate every open worksheet.
Click Calc Sheet to recalculate only the active worksheet.
TIP
Excel offers the following shortcut keys for recalculating without bothering with the Options dialog box:
-
F9Recalculates the changed formulas in all open worksheets
-
Shift+F9Recalculates the changed formulas in the current worksheet
-
Ctrl+Alt+F9Recalculates every formula (even those that are unchanged) in all open worksheets
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 Edit, 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.