Section 21.3. Multi-Layered Pivot Tables

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.

Figure 21-11. In this pivot table, rows are grouped by category and subdivided by order date. That means each row shows information for the orders made for a given product category on a given day. Each row is further broken up into columns by country. At the bottom of each group (not shown), Excel subtotals all the rows for that product across all days.

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:

  • Categories and Products . Each product is a part of one category. Thus, you can group first by category, and then subdivide each category by product.

  • Ship Country and Ship City . Each city is located in one country. You can group first by country, and then subdivide each country into cities.

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.

Figure 21-12. Top: When subgrouping, make sure you place your fields in the correct order. Here, the pivot table groups the records by product and then subdivides the products by category, which really doesn't make sense. The result is a table where each group contains a single subgroup .
Bottom: This pivot table's rows are grouped by category and then subdivided by product, which makes much more sense.

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.

  • To hide (or collapse ) the products in a specific category, click the plus/minus icon next to the category name . You can repeat this process to collapse as many categories as you want. All you'll see is the row with the category totals.

  • To show a collapsed category, click the plus/minus icon again.

    Tip: For an even quicker shortcut to hide or show a category, just double-click the cell with the category name (like Beverages in A6). When you double-click an expanded category, Excel collapses it. When you double-click a collapsed category, Excel expands it. This feature helps you quickly drill down to the most interesting parts of your summary.
  • To collapse all the categories in your pivot table, move to any category and choose PivotTable Tools Options Active Field Collapse Entire Field.

  • To expand all your categories, move to any category and choose PivotTable Tools Options Active Field Expand Entire Field.

Figure 21-13 shows a pivot table that takes full advantage of Excel's ability to hide and show details.

Figure 21-13. In this pivot table, rows are grouped by Category and subgrouped by Product. Columns are grouped by Country and subgrouped by City. All the category groups are collapsed except for Produce, and all the country groups are collapsed except for Austria. This way, the pivot chart highlights produce sales in Graz and Salzburg, two picturesque Austrian cities.

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.

Figure 21-14. This pivot table drills down through three levels of row groupings. It shows a detailed breakdown that indicates when Chef Anton's Gumbo Mix was ordered, and exactly where the shipments were headed.

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

Excel 2007[c] The Missing Manual
Excel 2007[c] The Missing Manual
ISBN: 596527594
Year: 2007
Pages: 173 © 2008-2017.
If you may any questions please contact us: