This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2007/2008

Cell References: Relative and Absolute

Cell references are an important part of creating formulas in Excel. Using cell references allows your formulas to update automatically if the value in a particular cell changes and can also assist you in updating formulas as cells are copied or moved. There are two basic types of cell references, each serving a different purpose.

Return to topRelative Cell References

References that will change in relation to the new location of the formula. Relative references identify cells based on their relationship to the cell containing the reference.

EXAMPLE: In the example shown here, the function in D2 calculates Bob's pay by multiplying his hours (C2) by his rate of pay (B2). Since the rate of pay varies by student, we can use the Fill Command to copy the function to the other rows in the spreadsheet. Because the cell references are relative, the function will adjust to calculate the values in the appropriate rows. In other words, when we copy the function to Anna's total, her pay will be based on C3*B3.
Relative cell reference

Return to topAbsolute Cell References

References that remain the same when a formula is copied to a new location. No matter where the formula or the values in the original cell are moved, the formula will continue to refer to the same cell.

EXAMPLE: In the example shown here, the function in C11 calculates Bob's pay by multiplying his hours (B11) by the rate of pay for all students (C9). Notice the $ before both the column and row IDs ($C$9). By making the reference to the rate of pay an absolute reference, we can use the Fill Command to copy the function to the other rows in the spreadsheet. The same rate of pay will be multiplied by the appropriate hours for each student. In other words, when we copy the function to Anna's total, her pay will be based on B12*$C$9.
Absolute cell reference

Relative and absolute cell references can be used in all situations that require cell references, including cell ranges and formulas.

A formula, cell range, or cell reference can have both relative and absolute components. By adding a dollar sign ($) before either the column or row location or both, that reference becomes absolute. When adding dollar signs to cell references, only the portion of the reference directly following the dollar sign is absolute. To keep the entire cell reference constant, place a dollar sign before both the column and row location.
EXAMPLE: $A$12

To make a cell reference absolute:

  1. Within the formula, before the part of the reference (i.e., row or column reference) that you would like to be absolute, type $
    OR
    In the Formula bar
    1. Click within the cell reference you want to change
    2. Windows: Press [F4]
      Macintosh: Press [Cmd] + [T]
      Part of your cell reference is changed.
      HINT: Pressing [F4] or [Cmd] + [T] will cause elements (i.e., row and column references) of your cell reference to toggle between being relative and absolute. If you do not get the desired reference the first time, continue pressing this key until the desired portions of your reference are relative and absolute.
      EXAMPLE: When pressing [F4] or [Cmd] + [T], your cell reference may cycle through the following progression: A12, $A$12, A$12, $A12, A12.
Excellence. Our Measure. Our Motto. Our Goal.