Microsoft Office Tutorials and References

In Depth Information

**Cell and Range References**

h
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

h
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 enter 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-3 demonstrates an absolute reference in a formula. Cell D2 contains a formula that

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

=(B2*C2)*$B$7

Figure 2-3:
This worksheet demonstrates the use of an absolute reference.

The reference to cell B7 is an absolute reference. When you copy the formula in cell D2 to the

cells below, the $B$7 reference always points to the sales tax cell. Using a relative reference (B7)

results in incorrect results in the copied formulas.

Figure 2-4 demonstrates the use of mixed references. Note the formula in cell C3:

=$B3*C$2

This formula calculates the area for various lengths (listed in column B) and widths (listed in row

2). After you enter the formula, it can then be copied down and across. Because the formula uses