O'Reilly logo

Office X for Macintosh: The Missing Manual by David Reynolds, Tonya Engst, Nan Barber

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

464 office x for macintosh: the missing manual
Absolute references, on the other hand, refer to a specific 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 first 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 final price (in column D, for example),
youd 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 first 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?
Making lists.
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 fields 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.
Formula
Fundamentals
chapter 12: basic excel 465
The List Manager offers a number of features that improve upon using regular spread-
sheet cells to store your lists (and upon Excel databases, as they were called in some
previous versions):
The list frame, a special border that appears when you click a list object, clearly
outlines your data. You don’t have to wonder which cells are meant to be part of
the list.
Excel keeps the column headings of a list completely separate from the data be-
neath them; they won’t disappear or get sorted into the rows of the list itself, as
might have happened outside a list object.
You always get an empty record row at the bottom of the list, making it easy to
add a new record; just click in the row and type.
Lists have pop-up AutoFilter menus that make it simple to sort their rows or even
filter them (so that only certain rows remain visible).
Unlike Excel databases of old, you can have more than one list per spreadsheet.
The List Manager feature, in other words, is ideal for tasks like these:
• Build a list of all of the DVDs in your vast collection and sort them by genre,
rating, number of stars in reviews, whether discs have director’s commentaries—
the possibilities are endless.
Figure 12-20:
List objects are among Excel’s
best new features. These self-
contained mini databases
can fold and spindle a list in
record time. The top row has
in-cell buttons for popping up
the AutoFilter menu. This
menu lets you sort your list
and temporarily hide any
unwanted data. In the
optional total row at the
bottom, you can quickly
insert formulas via in-cell
buttons that pop up the
Function menu.
List frame (thickens when the list object is selected)
New record row (accepts new data)
Total row (shows math, such as totals, for your columns)
Function menu (provides a quick way to do math)
Heading row (displays headings defined in the List Wizard)
AutoFilter menu (changes which rows you can see)
Excel, the
List Maker

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required