Excel Annoyances

RECALCULATION ANNOYANCES

RECALCULATE NOW!

The Annoyance:

The formulas in my workbooks use many values from other sources—and because the source data changes frequently, Excel has to recalculate the formulas constantly. This takes up so much of my computer’s processing power that much of the time it’s impossible to get anything done. Can I control when and how Excel recalculates my formulas?

The Fix:

To control when Excel recalculates the formulas in your workbook, select Tools → Options, and click the Calculation tab (shown in Figure 3-15).

Figure 3-17. Prevent unnecessary recalculations using the Calculation tab of the Options dialog box.

The default is Automatic, which means Excel will recalculate your workbook whenever the value in a cell used in any of the workbook’s formulas changes. If you click the “Automatic except tables” radio button, Excel only recalculates formulas that aren’t part of a data table (a data table is a range of cells that shows how changing certain values in your formulas affects the results of the formulas). If you select the Manual radio button, Excel will recalculate your formulas only when you press F9, or click the Recalculate Now button on the Calculation tab. Furthermore, unchecking the “Recalculate before save” box means Excel won’t update the formula results every time you save the workbook—it will do so only when you press F9, or click the Recalculate ...