Page 241 - Excel 2007 Bible

16_044039 ch11.qxp 11/21/06 11:04 AM Page 198
Part II
Working with Formulas and Functions
Specifying when formulas are calculated
You’ve probably noticed that Excel calculates the formulas in your worksheet immediately. If you change
any cells that the formula uses, Excel displays the formula’s new result with no effort on your part. All this
happens when Excel’s Calculation mode is set to Automatic. In Automatic Calculation mode (which is the
default mode), Excel follows these rules when it calculates your worksheet:
diately those formulas that depend on new or edited data.
n If Excel is in the middle of a lengthy calculation, it temporarily suspends the calculation when
you need to perform other worksheet tasks; it resumes calculating when you’re finished with your
other worksheet tasks.
n Formulas are evaluated in a natural sequence. In other words, if a formula in cell D12 depends on
the result of a formula in cell D11, Excel calculates cell D11 before calculating D12.
Sometimes, however, you may want to control when Excel calculates formulas. For example, if you create a
worksheet with thousands of complex formulas, you’ll find that processing can slow to a snail’s pace while
Excel does its thing. In such a case, set Excel’s calculation mode to Manual — which you can do by choos-
ing Formulas ➪ Calculation ➪ Calculation Options ➪ Manual (see Figure 11.15)
FIGURE 11.15 n When you make a change — enter or edit data or formulas, for example — Excel calculates imme-
You can control when Excel calculates formulas.
TIP If your worksheet uses any data tables (described in Chapter 36), you may want to select the
TIP
option labeled Automatically Except For Data Tables. Large data tables calculate
notoriously slowly. Note: A data table is not the same as a table created by choosing Insert ➪ Tables ➪
Table.
When you’re working in Manual Calculation mode, Excel displays Calculate in the status bar when you
have any uncalculated formulas. You can use the following shortcut keys to recalculate the formulas:
n F9: Calculates the formulas in all open workbooks.
n Shift+F9: Calculates only the formulas in the active worksheet. Other worksheets in the same
workbook aren’t calculated.
n Ctrl+Alt+F9: Forces a complete recalculation of all formulas.
NOTE Excel’s Calculation mode isn’t specific to a particular worksheet. When you change the
NOTE
Calculation mode, it affects all open workbooks, not just the active workbook.

198