This chapter is from the book
This chapter is from the book
This chapter is from the book
Controlling Worksheet Calculation
Excel always calculates a formula when you confirm its entry. In addition, the program normally recalculates existing formulas automatically when the data changes. This behavior works 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 gives you two ways to get started:
- Select Formulas, Calculation Options.
- Select File, Options and then click Formulas.
No matter which of these two options you use, 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. 341.
- Manual—Choose this mode to force Excel not to recalculate any formulas either until 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, select the formula bar, and then confirm the cell by either pressing Enter or clicking the Enter button.
- To recalculate a range, select the range; select Home, Find & Select, Replace or press Ctrl+H. Enter an equal sign (=) in both the Find What and Replace With boxes. Click Replace All. Excel "replaces" the equal sign in each formula with another equal sign. Even though this doesn't change anything, it forces Excel to recalculate each formula.