Microsoft Office Tutorials and References
In Depth Information
h Numbers or text strings
h Worksheet functions (such as SUM or AVERAGE)
A formula in Excel 2010 can consist of up to 8,192 characters. After you enter a formula into a
cell, the cell displays the result of the formula. The formula itself appears in the formula bar when
the cell is activated. For a better view of a lengthy formula, click and drag the thick border of the
formula bar to expand it vertically.
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).
Excel’s Calculation mode isn’t specific to a particular worksheet. When you change
Excel’s Calculation mode, it affects all open workbooks, not just the active workbook.