Calculating Formulas
You havve probably noticed that the formulas in your worksheet get calculated immediately. If you change any cells that the formula uses, the formula displays a new result with no effort on your part. This occurs when Excel's Calculation mode is set to Automatic. In this mode (the default mode), Excel follows certain rules when calculating your worksheet:
- When you make a change (enter or edit data or formulas, for example), Excel calculates immediately those formulas that depend on new or edited data.
- If working on a lengthy calculation, Excel temporarily suspends calculation when you need to perform other worksheet tasks; it resumes when you finish.
- Formulas are evaluated in a natural sequence. For instance, 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 may want to control when Excel calculates formulas. For example, if you create a worksheet with thousands of complex formulas, you may find that things can slow to a snail's pace while Excel does its thing. In this case, you can set Excel's calculation mode to Manual. Do this by choosing Formulas => Calculation => Calculation Options => Manual.
When you work in Manual calculation mode, Excel displays Calculate in the status bar when you have any uncalculated formulas. The Formulas => Calculation group contains two controls that when clicked, perform a calculation: Calculate Now and Calculate Sheet control. In addition to these controls, you can use the following shortcut keys to recalculate the formulas:
- F9: Calculates the formulas in all open workbooks (same as Calculate Now control).
- Shift+F9: Calculates only the formulas in the active worksheet. It does not calculate other worksheets in the same workbook (same as Calculate Sheet control).
- Ctrl+Alt+F9: Forces a complete recalculation of all open workbooks. Use it if Excel (for some reason) does not seem to return correct calculations.
- Ctrl+Shift+Alt+F9: Rechecks all the dependent formulas and then forces a recalculation of all open workbooks.
Contrary to what you might expect, Excel's Calculation mode is not specific to a particular worksheet. When you change Excel's Calculation mode, it affects all open workbooks-not just the active workbook. Also, the initial Calculation mode is set by the Calculation mode saved with the first workbook you open.
