- Understanding Formula Basics
- Understanding Operator Precedence
- Controlling Worksheet Calculation
- Copying and Moving Formulas
- Displaying Worksheet Formulas
- Converting a Formula to a Value
- Working with Range Names in Formulas
- Working with Links in Formulas
- Formatting Numbers, Dates, and Times
- From Here
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, 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 gives you two ways to get started:
- Select Formulas, Calculation Options.
- Select File, 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. 345.
- Manual—Select 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, click in the formula bar, and then confirm the cell (by pressing Enter or clicking the Enter button).
- To recalculate a range, select the range; select Home, Find & Select, Replace (or press Ctrl+H); and 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. This doesn’t change anything, but it forces Excel to recalculate each formula.