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
Meaning
Cell
Reference
Type of
Reference
$B$20
Absolute cell reference
Both column and row references remain the same when you copy
this cell, because the cell references are absolute.
B$20
Mixed reference
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.
$B20
Mixed reference
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.
B20
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 refl ect the new location.
To Enter a Formula Containing Absolute Cell References
$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
1
Press CTRL + HOME and then click
cell B5.
formula appears
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
reference.
Enter box
Type ) to complete the formula
(Figure 3–25).
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
absolute
cell B4 is
relative
Excel colors
border of
cells used in
formula
Figure 3–25
 
 
Search JabSto ::




Custom Search