Microsoft Excel 2007
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, but using cell references properly 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
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: If you copied a formula from column A to column B and the formula contained a relative cell reference to cell A12, the formula would change to refer to cell B12.
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: If a formula using an absolute cell reference to cell A12 was copied to another column, the cell reference to A12 would remain constant.
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- Click within the cell reference you want to change
- 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.
