Excel Tip of the Week: Calculation Options

Today’s topic is calculation options.

In Excel you can specify a few different ways as to HOW the program calculates your numbers. This might sound a bit odd to a few of you, but hopefully the explanations of the different options will clear things up.

There are a couple ways to view the calculation options. The first way is on the Ribbon under the Formula menu. If you click into the formula menu and then look to the far right you will see the calculation options. (Keyboard shortcut Alt → M → X) However, the options shown in the Ribbon are a small sampling of the full set of calculation options available (and on a Mac it is even more limited).

The other way to look at the calculation options is to open the Excel Options (File → Options) and then clicking on “Formulas.” (Mac: From the menu bar click on Excel then Preferences then select the Calculation icon.)

Excel gives three different calculation options that are mostly self-explanatory:

1)      Automatic: This is the default mode. In this mode Excel will recalculate every formula in the spreadsheet each time something changes.

2)      Automatic except for data tables: This mode will update everything just like the above option, but it will not update data tables. This is really useful because data tables are very processor intensive and if you have multiple data tables you might have to wait a second or two for Excel to refresh after each change you make in the spreadsheet.

3)      Manual: Excel won’t calculate anything until you tell it to do so. This option is useful on really large spreadsheets that can take a while to calculate or if you’re working on a computer that is really slow. You can turn calculations off, input all your data, and then tell Excel to calculate everything once you’re done. Use caution however, because you could forget to calculate and then end up displaying incorrect data. The keyboard shortcut to calculate manually is F9 (Mac: CMD + =)

Iterative Calulations
The other calculation option of note is Iterative calculations. By default Excel turns this option off. This feature allows Excel to handle circular logic.

For example, to know the total cost of a project you need to know what the consulting fee will be, but the consulting fee is based on a percentage of the total cost of the project. Circular.

Excel is able to quickly try several sets of numbers to arrive at the correct answer despite the circular logic of your formulas. The “Maximum iterations” and the “Maximum Change” are set to 100 and .001 by default and in almost every situation those numbers are sufficient.

Be sure to check back in next week for the last ever Excel Tip of the Week!