Microsoft Office Tutorials and References

In Depth Information

•
F9:
Calculates the formulas in all open workbooks (same as the 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 the Calculate Sheet control).

•
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.

•
Ctrl+Shift+Alt+F9:
Rechecks all the dependent formulas and then forces a recalculation of all open work-

books.

Contrary to what you might expect, Excel's Calculation mode isn't specific to a particu-

lar workbook. When you change Excel's Calculation mode, it affects all open work-

books — 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 use of the dollar sign symbol differentiates them:

•
Relative:
The reference is fully relative. When you copy the formula, the cell reference adjusts to its new

location.

Example: A1

•
Absolute:
The reference is fully absolute. When you copy the formula, the cell reference does not change.

Example: $A$1

•
Row Absolute:
The reference is partially absolute. When you copy the formula, the column part adjusts, but

the row part does not change.

Example: A$1

•
Column Absolute:
The reference is partially absolute. When you copy the formula, the row part adjusts, but

the column part does not change.

Example: $A1

Creating an absolute or a mixed reference

When you create a formula by pointing to cells, all cell and range references are relative. To change a reference

to an absolute reference or a mixed reference, you must do so manually by adding the dollar signs. Or when

you're entering a cell or range address, you can press the F4 key to cycle among all possible reference modes.

If you think about it, you may realize that the only reason you would ever need to change a reference is if you

plan to copy the formula.

Figure 2-4 demonstrates an absolute reference in a formula. Cell D2 contains a formula that multiples the quant-

ity (cell B2) by the price (cell C2) and then by the sales tax (cell B7):