Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

You've probably noticed that the formulas in your worksheet get calculated immediately. If you change a cell that a formula uses, the formula displays a new result with no effort on your part. This is what happens when the Excel Calculation mode is set to Automatic. In this mode (which is the default mode), Excel uses the following rules when calculating your worksheet:

• When you make a change - enter or edit data or formulas, for example - Excel immediately calculates those formulas that depend on the new or edited data.

• If it's in the middle of a lengthy calculation, Excel temporarily suspends calculation when you need to perform other worksheet tasks ; it resumes when you're finished.

• Formulas are evaluated in a natural sequence. In other words, if a formula in cell D12 depends on the result of a formula in cell D11, cell D11 is calculated before D12.

Sometimes, however, you might want to control when Excel calculates formulas. For example, if you create a worksheet with thousands of complex formulas, operations can slow to a snail 's pace while Excel does its thing. In such a case, you should set Excel's calculation mode to Manual. Use the Calculation Options control in the Formulas Calculation group .

When you're working in Manual Calculation mode, Excel displays Calculate in the status bar when you have any uncalculated formulas. You can press the following shortcut keys to recalculate the formulas:

• F9 calculates the formulas in all open workbooks.

• Shift+F9 calculates the formulas in the active worksheet only. Other worksheets in the same workbook won't be calculated.

• Ctrl+Alt+F9 forces a recalculation of everything in all workbooks. Use it if Excel (for some reason) doesn't seem to be calculating correctly, or if you want to force a recalculation of formulas that use custom functions created with Visual Basic for Applications (VBA).

• Ctrl+Alt+Shift+F9 rechecks all dependent formulas, and calculates all cells in all workbooks (including cells not marked as needing to be calculated).