Microsoft Office Tutorials and References
In Depth Information
Once this tableau is ushered onto the screen, you can click on or drag the fields in the PivotTable
Field List into the appropriate button areas, just as if you were constructing a pivot report. Just
remember however that here, Row Labels is called Axis Fields (Categories), and the Columns Labels
area reverts to Legend Fields (Series). By simply clicking the checkboxes by Salesperson and Order
Amount, I’ll produce precisely the same chart you see in Figure 8–70).
And if you want to delete a pivot report, click anywhere in the report and click PivotTable Tools
Options tab the Select down arrow in the Actions button group Entire PivotTable . Then press the
Delete key. To delete a pivot chart, just select it (you can select it when you see the four-side arrow
over the chart) and press Delete. Interestingly enough, if you delete a pivot report that has been used
as the source of a pivot chart, deleting the report leaves the chart onscreen, even though it’s no longer
con n e cte d wi th a n y da ta . Howe v e r , i f y ou cl i ck Opti on s Actions Clear Clear All , you’ll delete both
the PivotTable and its associated chart.
Once you get the hang of them, pivot reports and charts equip you with a potent and agile means for
aggregating large amounts of data into informative categories. PivotTables grant you the ability to
answer these kinds of questions:
How many sales did each salesperson achieve?
What’s the average grade point average of students, broken out by their major?
How much mon e y di d we spe n d on tr an spor tati on i n Jan uar y ?
Who were in the top 5 percent off all home run hitters last year?
The key to understanding PivotTables is to understand which data goes where, and, prior to
actually composing the tables, thinking about how to arrange the data that contribute to them. For
example, our salesperson workbook could have assigned a different column to each salesperson, but
that would greatly complicate the data aggregation tasks at which PivotTables are so adept. Try that
approach and you’ll see what I mean; you’ll have more aggravation than aggregation.
Remember that the data you want counted, summed, or averaged go in the Values area. The
categories by which the data are broken out go into the Row and/or Column Labels, or Report Filter
areas. It’s tempting to say that by merely clicking and dragging various source data fields around the
pi v ot r e por t y ou ca n e xpe r i me n t a n d si mpl y se e wha t ha ppe n s. But i t r e a l l y he lps to a ppr e ci a te how the
areas interact with one other—the what-goes-where question-and that appreciation will speed the
table construction process.
It’s true—nail these concepts down and you can assemble a PivotTable in about four seconds.
But now that you’ve mastered all these sophisticated number-crunching techniques, you still need
to know how to bring these results to good old hard copy pages—at least once in a while. Next up:
Printing in Excel.