Datasheet

115
CHAPTER 4
■
WORKING DATA MAGIC WITH CALCULATIONS
For any cell, there is only one reference but four reference types: rela-
tive, absolute, and two mixed types. Dollar signs ($) in the reference deter-
mine the type:
■
A1 is called relative.
■
$A$1 is called absolute.
■
$A1 and A$1 are called mixed.
An absolute cell reference is a fixed geographical point, like a street
address, such as 123 Cherry Street.
A relative cell reference is a relative location, as in “one block west and
two blocks south.”
The mixed cell reference is a mixture of absolute and relative loca-
tions, as in “three blocks east on Hampden Avenue.” A mixed cell refer-
ence can have an absolute column and relative row, as in $A1, or a
relative column and absolute row, as in A$1.
The dollar signs designate the row and/or column as absolute, or
unchanging, within a reference. When you write formulas, the meetings
of absolute, relative, and mixed become more clear (see Figure 4.10).
Figure 4.10. The four reference types
Changing reference types
If you need to change a cell reference type in a formula, there’s a much
faster way than typing dollar signs ($).
The fastest way to change cell reference types is to cycle through the
four types until you find the one you want: Double-click the cell contain-
ing the formula, and within the formula, click in the cell reference you
want to change (see Figure 4.11). Press F4 until the reference changes to
the type you want (pressing F4 repeatedly cycles through all the possible
reference types).
When the reference type changes to the type you want, you can either
click in another cell reference to change it or press Enter to finish.
Relative
Absolute Mixed
09_763217 ch04.qxp 1/18/06 11:23 PM Page 115