Microsoft Office Tutorials and References

In Depth Information

**Cell and Range References**

h
If working on a lengthy calculation, Excel temporarily suspends calculation when you

need to perform other worksheet tasks; it resumes when you finish.

h
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. In addition to these

controls, you can use the following shortcut keys to recalculate the formulas:

➜

h
F9:
Calculates the formulas in all open workbooks (same as the Calculate Now control).

h
Shift+F9:
Calculates only the formulas in the active worksheet. It does not calculate other

worksheets in the same workbook (same as the Calculate Sheet control).

h
Ctrl+Alt+F9:
Forces a complete recalculation of all open workbooks. Use it if Excel (for

some reason) doesn’t seem to return correct calculations.

h
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 isn’t 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 that you open.

Cell and Range References

Most formulas reference one or more cells by using the cell or range address (or the name if it

has one). Cell references come in four styles; the dollar sign differentiates them:

h
Relative:
The reference is fully relative. When you copy the formula, the cell reference

adjusts to its new location.

Example: A1

h
Absolute:
The reference is fully absolute. When you copy the formula, the cell reference

does not change.

Example: $A$1