Microsoft Office Tutorials and References

In Depth Information

**Cell and Range References**

Cell and Range References

Most formulas refer to one or more cells. You can make cell references by using the cell’s or

range’s address or name (if it has one). Cell references come in four styles:

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

adjusts to its new location. Example: A1.

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

doesn’t change. Example: $A$1.

h
Row Absolute:
The reference is partially absolute. When the formula is copied, the

column part adjusts, but the row part doesn’t change. Example: A$1.

h
Column Absolute:
The reference is partially absolute. When the formula is copied, the

row part adjusts, but the column part doesn’t change. Example: $A1.

By default, all cell and range references are relative. To change a reference, you must manually

add the dollar signs. Or, when editing a cell in the formula bar, move the cursor to a cell address

and press F4 repeatedly to cycle through all four types of cell referencing.

Why use references that aren’t relative?

If you think about it, you’ll realize that the only reason why you would ever need to change a

reference is if you plan to copy the formula. Figure 3-1 demonstrates why this is so. The formula in

cell C3 is

=$B3*C$2

Figure 3-1:
An example of using nonrelative references in a formula.

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

3). After the formula is entered, you can then copy it down to C7 and across to F7. Because the

formula uses absolute references to row 2 and column B and relative references for other rows

and columns, each copied formula produces the correct result. If the formula used only relative

references, copying the formula would cause all the references to adjust and thus produce

incorrect results.