Microsoft Excel 2007/2008: Cell References: Relative and Absolute (Win/Mac)

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.

• Relative Cell References
• Absolute Cell References

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

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

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:

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.

 

Make sure to let me know in the comments below or on our Facebook page how you've got with it or Do you have any questions