Microsoft Office Tutorials and References

In Depth Information

Absolute versus Relative Addressing

EX 187

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 reﬂ 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
F4
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.

•
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

formula appears

in formula bar

•
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 B4 is

relative

cell $B$21 is

absolute

Excel colors

border of

cells used in

formula

Figure 3–25