464 ofﬁce x for macintosh: the missing manual
Absolute references, on the other hand, refer to a speciﬁc cell, no matter where the
formula appears in the spreadsheet. They can be useful when you need to refer to a
particular cell in the spreadsheet—the one containing the sales tax rate, for example—
for a formula that repeats over several columns. Figure 12-19 gives an example.
You designate an absolute cell reference by including a $ in front of the column and/
or row reference. (For the ﬁrst time in its life, the $ symbol has nothing to do with
money.) For example, $A$7 is an absolute reference for cell A7.
You can also create a mixed reference in order to lock the reference to either the row
or column—for example, G$8, in which the column reference is relative and the
row is absolute. You might use this unusual arrangement when, for example, your
column A contains discount rates for the customers whose names appear in column
B. In writing the formula for a customer’s ﬁnal price (in column D, for example),
you’d use a relative reference to a row number (which is different for every cus-
tomer), but an absolute reference to the column (which is always A).
Tip: Here’s a handy shortcut that can save you some hand-eye coordination when you want to turn an
absolute cell reference into a relative one, or vice versa: First, select the cell that contains the formula. In
the Formula bar, highlight only the cell name you’d like to change. Then press c-T. This keystroke makes
the highlighted cell name cycle through different stages of absoluteness—for example, it changes the cell
reference B4 ﬁrst to $B$4, then to B$4, then to $B4, and so on.
Excel, the List Maker
After spending years loading up Excel with advanced number-crunchy features like
pivot tables, database queries, and nested formulas, in 1999 Microsoft decided to
step back and conduct some studies to see how its customers were enjoying their
NASA-caliber spreadsheet program.
And what were 60 percent of Excel users doing with all this power?
That’s right—most people use the software that drives uncounted businesses and
statistical analyses for nothing more than building lists of phone numbers, CD col-
lections, and so on.
That’s why Microsoft, which never met a feature it didn’t like, added to Excel 2001
the Macintosh-only List Manager, which makes building and manipulating lists easy
(Figure 12-20). Excel does this by creating something called a list object, which is
nothing more than a simple database. It’s made up of rows (which are the same as
database records—that is, the individual “rolodex cards” of an address database) and
columns (which are like the ﬁelds in a database record—that is, the address, city, zip
code, and other bits of information). These rows and columns are contained inside
a list frame.