Relative And Absolute References In Formulas
This page describes relative and absolute cell references in
Excel formulas.
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. For example A1 is a relative range, while $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:
Reference Style |
Meaning |
$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).
Even with an absolute referencing style, Excel will still change row and column
references when you insert a row or column. To have a truly absolute cell
reference that will not change under any circumstances, use the INDIRECT
function. For example =INDIRECT("A1") will always refer to cell A1, regardless
of any changes made to the worksheet. This works because Excel does not
interpret the string "A1" as an address. Instead, it treats it as plain text and
therefore does not change it.
A common use of mixing absolute and relative range specifications is to create a
running total of a column of number. For example, if you have data in cells
A1:A10, the formula =SUM(A$1:A1) in cell B1 and filled down to cell B10 will
return the running total for the numbers in column A.
This page last modified on 4-July-2007.