Datasheet

Working with PivotTables
I could talk about PivotTables until I am blue in the face, but it’s much better to actually
show an example. By looking at the kind of data that PivotTables are used for, and seeing
the resulting PivotTable in action, you will get a good understanding of the what and why
of this powerful tool.
Figure 1.1 shows some data that are typical of the kind you would analyze using a
PivotTable. These data are based on the sporting-goods store example I mentioned earlier.
As with other examples in this book I have intentionally simplified the data to illustrate
the points I am trying to make without confusing you with unnecessary details. You should
not think that PivotTables are limited to relatively simple data such as these!
What questions might you want to ask about these data? Here are a few that come to mind:
• What are the sales for the Camping category for each region?
• In each store, which days of the week see the most customers?
• In each store, which category has the highest sales?
• Which day of the week has the lowest total sales?
In the following demonstration you explore the first question. You create a PivotTable
report that shows the total sales of goods in the Camping category subtotaled by region.
Part I: Understanding PivotTables and Charts
4
If you have used older versions of Excel, you may be wondering how PivotTables relate
to another Excel data analysis technique, the crosstab table. The fact is that PivotTables
are a replacement for crosstabs, which are not even supported in newer versions of
Excel. PivotTables are significantly more powerful than crosstabs and are easier to use.
If you find yourself working with an old workbook that contains a crosstab table, your
best bet is to convert it to a PivotTable report. Then you’ll have the power of the
PivotTable at your fingertips if you need to change the way the data is analyzed. To
convert a crosstab to a PivotTable, follow these steps:
1. Open the workbook that contains the crosstab table.
2. Click any cell in the crosstab table.
3. Select Pivot Table and Pivot Chart Report from the Data menu.
4. Click Finish and then click OK in response to any prompts.
5. Save the workbook in the current Excel version.
Of course you should not do this if you or someone else will later need to open the
workbook in the older version of Excel.
What About Crosstab Tables?
03_772402 ch01.qxd 11/30/05 9:41 PM Page 4