Absolute and Relative Cell Reference

Excel accepts cell references in what are called absolute and relative ranges.

Absolute ranges have a $ character before the column portion of the reference and/or the row portion of the reference.

Relative ranges do not use the $ character. The $ character indicates to Excel that it should not increment the column and/or row reference as you fill a range with a formula or as you copy a range.

Example
A1 is a relative range
$A$1 is an absolute range.
If you enter =A1 in a cell and then fill that cell down a column the ‘1’ in the reference will increment in each row. Thus the formula in row 50 would be =A50.
However if you enter =$A$1 in a cell and fill down, the range reference will remain $A$1 — it will not increment as you fill or copy down a column.

There are three absolute styles:

$A$1 Both the column and row reference are fixed. Neither will be incremented or changed during a copy or fill operation.
$A1 Only the column reference is fixed. It will not change during a fill or copy, but the row will change.
A$1 Only the row reference is fixed. It will not change during a fill or copy, but the column will change.

If you select all or part of a formula in the formula you can press F4 to cycle range reference between the 4 styles (1 relative and 3 absolute).




2 Comments

Leave a Reply