Microsoft Office Tutorials and References
In Depth Information
Specifying when formulas are calculated
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:
When you make a change — enter or edit data or formulas, for example — Excel
calculates immediately those formulas that depend on new or edited data.
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 fi nished with your other worksheet tasks.
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 cell 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’fill fi nd 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 choosing Formulas
Calculation
Calculation
Options Manual (see Figure 15.15).
FIGURE 15.15
You can control when Excel calculates formulas.
If your worksheet uses any data tables, you may want to select the Automatically Except for Data Tables option.
Large data tables calculate notoriously slowly. Note: A data table is not the same as a table created by choosing
Insert ➪ Tables ➪ Table.
15
Search JabSto ::




Custom Search