By Ken Bluttman

The Excel default is to calculate your formulas automatically as they are entered or when you change the worksheet. In some situations, you may want to set the calculation to manual. Leaving the setting on automatic is usually not an issue, but if you are working on a hefty workbook with lots of calculations, you may need to rethink this one.

Imagine this: You have a cell that innocently does nothing but display the date. But dozens of calculations throughout the workbook reference that cell. Then dozens more calculations reference the first batch of cells that reference the cell with the data. Get the picture? In a complex workbook, there could be a lot of calculating going on, and the time it takes can be noticeable.

Turning the calculation setting to manual lets you decide when to calculate. Do this in the Excel Options dialog box; click the File tab on the Ribbon and then click Options. In the dialog box, click the Formulas tab, in which calculation options are selected, as shown here. You can select one of the automatic calculation settings or manual calculation.

Setting the calculation method.
Setting the calculation method.

Pressing F9 calculates the workbook. Use it when the calculation is set to Manual. Here are some further options:

What You Press What You Get
F9 Calculates formulas that have changed since the last
calculation, in all open workbooks
Shift+F9 Calculates formulas that have changed since the last
calculation, just in the active worksheet
Ctrl+Alt+F9 Calculates all formulas in all open workbooks, regardless of
when they were last calculated