21.3. Multi-Layered Pivot Tables
So far, you've seen examples of how to create one- and two-dimensional pivot tables. But the fun doesn't need to end there. In fact, there's no limit to the number of groupings you can add to a pivot table. To add additional levels of grouping, simply drag the appropriate fields from the PivotTable Field List onto the row or column area of the pivot table. Each time you add a new grouping, Excel subdivides your current groups.
If you add Product to the row area of your pivot table, Excel groups all your records into rows so that each row totals the information for a separate product. Next , say you add another field to the row areathis time, the Order Date field. Excel responds by dividing each product row into multiple rows. Each individual row shows the total units for a given product sold on a given day.
The problem with subdividing is that it can needlessly enlarge the size of your summary table. If you're not careful, your summary table may not be a summary at all! Consider the table in Figure 21-11, which shows the Category and Order Date fields in the row area, and the Ship Country field in the column area. The problem here is that there aren't many orders that fall on the same date. Even when they do, they're often for products in different categories. As a result, many of the rows aren't true totalsinstead, they display only the results for a single order.
One hint that the table isn't performing a good summary is the number of rows in the pivot table. At 1,621 rows, it's not much smaller than the total 2,155 rows of source data in the original table. You'll also notice that the table is quite sparse because each row is further broken down into columns (by country). The end result is a lot of blank, wasted space.
This example may lead you to conclude that pivot tables with more than two groupings aren't much useunless you have a staggering amount of data (thousands or even tens of thousands of records). However, subgrouping does come in handy if you have related fields. There are two good examples of related fields in the orders table in Figure 21-11:
In both of these examples, it's important to make sure you add the fields in the correct order. You don't want to group by product and then group by category, because each product is only a part of one category. Instead, you want to group by category, and then list products within these categories. Figure 21-12 shows the difference.
To make sure you have the right grouping, look in the Row Labels box (if you're grouping rows) or the Column Labels box (if you're grouping columns) in the PivotTable Field List pane. In the example shown in Figure 21-12, bottom, the Category field should appear above the Product field in the list. If it isn't, you can drag the Category field into place, or you can click it and choose Move Up from the pop-up menu.
21.3.1. Hiding and Showing Details
Subgrouping gives you another interesting abilityyou can hide or show individual groups. This feature lets you show detailed information for just the part of the table that you're interested in, while hiding the rest. In fact, this feature works just like the collapsible outlines you learned about in Chapter 15.
Imagine you create a pivot table that uses the Category and Product fields to group rows. When you create this table, Excel shows you every product in every category. But what if you want to show only the products in a specific category? In this case, the trick is to hide every category you don't want to see.
Figure 21-13 shows a pivot table that takes full advantage of Excel's ability to hide and show details.
There's no limit to how many levels of grouping you can add. If you use the Show Detail command to try and expand the last level of your pivot table, Excel prompts you with a Show Detail dialog box that lists all the fields you aren't using currently. If you choose one of these fields and click OK, Excel adds another layer of grouping to the pivot table, as shown in Figure 21-14.
Tip: You can see a screencast an online, animated tutorialthat demonstrates how to dig through the levels of a multi-layered pivot table on the "Missing CD" page at www.missingmanuals.com.