Choosing the Right Calculation Preferences for Your Needs

If you create complex worksheets, you may need to change the calculation settings Excel uses—for example, by switching from automatic recalculation to manual recalculation, by limiting the number of iterations Excel performs, or by changing values from full precision to the number of decimal places actually shown in the cells.

To change the calculation settings, open the Calculation preferences pane (see Figure 6–8) like this:

1. Choose Excel Preferences or press Cmd+, (Cmd and the comma key) to display the Excel Preferences dialog box.
2. In the Formulas and Lists area, click the Calculation icon.

Figure 6–8. In the Calculations preferences pane of the Excel Preferences dialog box, you can choose when to calculate worksheets, limit iteration, and change workbook-specific options.

Choosing When to Calculate Worksheets

In the Calculate sheets box, first choose how to recalculate the worksheets by selecting the appropriate option button:

• Automatically. Select this option button to have Excel recalculate all dependent formulas every time you change a value. This is the default setting and ensures your worksheets remain up-to-date. Use this setting unless your worksheets are complex and recalculating them becomes slow.
• Automatically except for data tables. Select this option button to recalculate all dependent cells except those in data tables each time you change a value. Use this setting when recalculating data tables automatically becomes too slow. You can then recalculate data tables manually as needed.
• Manually. Select this option button to turn off automatic recalculation altogether. Use this setting for large and complex worksheets in which automatic recalculation becomes slow enough to be disruptive.

If you select the Manually option button, select the Always calculate before saving workbook check box if you want Excel to recalculate each workbook when you save it. The advantage to doing this is that it ensures that the saved version of the workbook displays the correct values, which is especially important the next time you open the workbook. The disadvantage is that saving the workbook takes much longer.

You can force recalculation of the entire workbook by clicking the Calc Now button in the Calculate sheets box in the Calculation preferences pane, or force recalculation of the active worksheet by clicking the Calc Sheet button. But normally you'll find it easier to recalculate by choosing Formulas Calculation Recalculate All (or pressing Cmd+=) or Formulas Calculation Recalculate Sheet from the Ribbon.

Controlling Iteration of Calculations

When Excel runs into a circular reference, it stops automatically either after 100 iterations of the calculation or when all the values change by less than 0.001. These settings work well for general use—especially if your worksheets don't need circular references (as is the case for many worksheets).

If your worksheets do need circular references, you can control the iteration of calculations by adjusting the settings in the Iteration box in the Calculation preferences pane. Select the Limit iteration check box, and then adjust the values in the Maximum iterations text box and the Maximum change text box as necessary.

Choosing Workbook Options

In the Workbook options box in the Calculation preferences pane, you can choose three settings that affect only the active workbook:

• Set precision as displayed. Select this check box if you want Excel to change the values in the workbook's cells to match the values that are displayed in the cells. Excel stores values using 15 digits, which is called full precision. You can format cells to display only as many decimal places as you want. For example, you may need to see currency amounts with two decimal places (such as $110.28), even though the number Excel is storing has further digits (such as 110.2769821). If you select the Set precision as displayed check box, Excel changes the underlying value to match the displayed value—for example, changing 110.2769821 to 110.28.

• Use the 1904 date system. Select this check box to use serial dates starting with 2 January 1904 rather than 1 January 1901, the standard start date. Normally, you will need to do this only if you're working with dates in workbooks developed in earlier versions of Excel:Mac.
• Save external link values. Select this check box if you want Excel to save values from an external data source in the workbook. This is usually a good idea, but you may need to clear this check box if you're bringing in large amounts of data from an external source, as saving the data in Excel may take a long time or make the workbook large and unwieldy.

When you've finished choosing Calculation preferences, click the OK button to close the Excel Preferences dialog box.