Distinguishing Absolute and Relative Cell References
Use absolute and relative forms of a cell reference to indicate the cell to which you
want the reference to point if you copy or move its formula.
If a cell reference is relative (A1), when its formula moves, it stays the same. However,
when the formula is cut or copied and then pasted, the cell reference changes so
that it retains the same position relative to the formula cell. For example, if a formula
containing A1 appears in C4 and you copy the formula and paste it in C5, the cell
reference in C5 becomes A2.
If the row and column components of a cell reference are absolute ($A$1), when its
formula is moved or copied and pasted, the cell reference doesn’t change. You use the
dollar sign ($) to designate as absolute a row or column component. For example, if a
formula containing $A$1 appears in C4 and you copy the formula and paste it in C5 or
to D5, the cell reference in C5 or D5 remains $A$1.
If the row component of a cell reference is absolute (A$1), the column component is
relative and may change to retain its position relative to the formula cell. For example,
if a formula containing A$1 appears in C4 and you copy the formula and paste it in D5,
the cell reference in D5 becomes B$1.
If the column component of a cell reference is absolute ($A1), the row component is
relative and may change to retain its position relative to the formula cell. For example,
if a formula containing $A1 appears in C4 and you copy the formula and paste it in C5
or D5, the cell reference in C5 and D5 becomes $A2.
If both the formula cell and its referenced cells are selected, when you move the
selection, the formula stays the same, regardless of the relative or absolute settings of
the cell references.
Here are ways to specify the absoluteness of cell reference components:
Type the cell reference using one of the conventions described above.
m
Click the disclosure triangle of a cell reference and choose an option from the
m
pop-up menu.
Select a cell reference and press Command-K to cycle through options.
m