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 ﬁ 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 ﬁ 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