excel Calculating Formulas

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:
h 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.
h 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.
h 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, calculation might slow things down.
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
h F9 calculates the formulas in all open workbooks.
h Shift+F9 calculates the formulas in the active worksheet only. Other worksheets in the
same workbook won’t be calculated.
h 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).
h Ctrl+Alt+Shift+F9 rechecks all dependent formulas and calculates all cells in all workbooks (including cells not marked as needing to be calculated).