Microsoft Office Tutorials and References

In Depth Information

**Calculating Formulas**

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.

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

formulas:

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.