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.
Student Rate $8.50
Student Hour Total
Bob 30 $255.00
Ann 35 $297.50
May 25 $212.50
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(constant). 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
Click within the cell reference you want to change
Windows: Press [F4]
Part of your cell reference is changed.
HINT: Pressing [F4] 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]your cell reference may cycle through the following progression: A12, $A$12, A$12, $A12, A12.
Go to the SeaJte\Excel 2007\IF function folder view the tutorial video to complete today's assignment. You may find the Payroll assignment on the same folder.
