Microsoft Office Tutorials and References
In Depth Information
To Enter a Formula Containing Absolute Cell References
Table 3–7 Examples of Absolute, Relative, and Mixed Cell References
Absolute cell reference
Both column and row references remain the same when you copy
this cell, because the cell references are absolute.
This cell reference is mixed. The column reference changes when
you copy this cell to another column because it is relative. The row
reference does not change because it is absolute.
This cell reference is mixed. The column reference does not change
because it is absolute. The row reference changes when you copy
this cell reference to another row because it is relative.
Relative cell reference
Both column and row references are relative. When copied to
another cell, both the column and row in the cell reference are
adjusted to reﬂ ect the new location.
$B$21) in cell B5 using Point mode. To enter
an absolute cell reference, you can type the dollar sign ($) as part of the cell reference or enter it by pressing F 4 with
the insertion point in or to the right of the cell reference to change to absolute.
The following steps enter the cost of goods formula = B4*(1
• Press CTRL + HOME and then click
in formula bar
• Type = (equal sign), click cell B4,
type *(1-b21 and then press F4
to change b21 from a relative
cell reference to an absolute cell
• Type ) to complete the formula
Is an absolute reference required
in this formula?
No, because a mixed cell reference
could have been used. The formula
in cell B4 will be copied across
columns, rather than down rows.
So, the formula entered in cell B4
in Step 1 could have been entered
as =B4*(1-$B21), rather than
=B4*(1-$B$21). That is, the formula
could have included the mixed
cell reference $B21, rather than
the absolute cell reference $B$21.
When you copy a formula across
columns, the row does not change
anyway. The key is to ensure that
column B remains constant as you
copy the formula across rows. To
change the absolute cell reference
to a mixed cell reference, continue
to press the F 4 key until you get
the desired cell reference.
cell $B$21 is
cell B4 is
cells used in