Microsoft Office Tutorials and References
In Depth Information
PivotTables and Pivot Charts
—that list of family expenses. Turn this database into a table, as we saw in Chapter 6, and you could, for
example, filter Newspaper and/or Telephone expenses and calculate their respective contributions to
the budget—and an Advanced Filter would enable you to save those data to another location on the
wor k she e t. You coul d a l so g e n e r a te a count of the number of Newspaper or Telephone payments, by
clicking on Count Number in the Expenses cell in the table’s Total row.
But if you need a more comprehensive breakout—say you want to see Newspaper costs by each
month , in which three variables, or fields, are considered at the same time—Expense, Date, and Sum—
then the task becomes more challenging. My professional recommendation: you need a PivotTable.
Indeed, the revised budget portrait we presented in Chapter 1 (Figure 8–2):
Figure 8–2. The budget data broken out by category and month
was engineered by a PivotTable. Data from two fields, Expense and Date, form a set of borders around
the actual expense data, which are then broken out into individual totals at the intersections of each
Expense and each Date, in this case defined in monthly units (of course some of the intersections are
blank, simply because that type of expense wasn’t incurred that particular month). It’s this sort of thing
that a PivotTable can do with ease, and the sort of thing that a table can’t.
This object used to be called a crosstab—a kind of matrix in which data were organized by one
v ar i abl e di spl ay e d hor i zon tal l y (i n the abov e case , by Date ), an d an othe r di spl ay e d v e r ti cal l y
(Expense). PivotTables are latter-day, electronic descendants of the crosstab, and once you acquire a
basic understanding of how they work, you can cook one up in about four seconds—at the same speed
as an Excel chart.
It’s true that PivotTables are viewed by some users as a rather forbidding and daunting object-and
I’ll allow that when I first set eyes on a PivotTable, in pre-millennium New York, I had no clue what they
were about. By the second look, however, PivotTables began to make considerably more sense—and if
you under stand what’ s going on in the scr een shot above, and doubtl ess you do-you’ r e on your way.
Of course we can’t tell you everything about PivotTables in this chapter—whole books have been
devoted to just this topic (two by Apress, by the way). But we are going to describe the PivotTable
essentials-and then some—so you’ll be able to do quite a bit of productive work with them.