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.                      Search JabSto ::

Custom Search