How to Build Excel 2007 Formulas with the Insert Function Dialog Box
How to Display All Formulas in an Excel 2007 Worksheet
Excel Cell References Worth Remembering

Controlling Excel 2007 Formula Calculations Manually

By default, Excel 2007 calculates your formulas automatically as they are entered or when you modify the worksheet. You can switch to manual calculation when necessary. Leaving the setting on automatic is usually not an issue, but if you're 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 then there are dozens of calculations throughout the workbook that reference that cell. Then there are dozens more calculations that reference the first batch of cells that reference the cell with the date. Get the picture? In a complex workbook there could be a lot of calculating going on. And the time it takes to recalculate all those formulas can be noticeable.

Turning the calculation setting to manual lets you decide when to calculate. To easily switch between automatic and manual recalculation, click the Calculation Options button on the Formulas tab and select the option you want: Automatic, Automatic Excel for Data Tables, or Manual.

Additional settings relating to recalculation options are available in the Excel Options dialog box. Follow these steps:

  1. Click the Office button and then click the Excel Options button.

    The Excel Options dialog box appears.

  2. Click the Formulas tab.

    The Formulas options appear in the right pane.

  3. Change the desired settings in the Calculation Options section at the top of the Formulas tab and then click OK.

    Use the Excel Options dialog box to set additional recalculation options.
    Use the Excel Options dialog box to set additional recalculation options.

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

Calculation Options in Excel
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

The calculation setting has no effect on whether you can enter a function. Even with calculation set to Manual, you can enter functions and get an initial returned value. The only catch is that the value won’t update without pressing F9.

blog comments powered by Disqus
Finding the Correct Function in Excel 2007
Representing Arrays in Excel 2007 Formulas
Assigning Names to Constant Values in Excel 2007
Editing an Array Formula in Excel 2007
Referring to Values or Formulas in Other Cells in Excel 2007 Formulas