Section 20.3. Advanced Pivot Tables | Excel 2010: The Missing Manual

20.3. Advanced Pivot Tables

Now that you've learned the basics of pivot tables, it's time to add a few more tricks to your arsenal. With the techniques demonstrated in the next few sections, you'll learn how to create pivot tables that have multiple levels, filter out just the records you want to analyze, and use custom calculations.

20.3.1. Creating 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.

For example, imagine 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 OrderDate 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 might not be a summary at all! For example, consider the table in Figure 20-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 20-11. Top : 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.
Bottom : At the bottom of each group , 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 might 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 20-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. For example, 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 20-12 shows the difference.

Figure 20-12. Top : When subgrouping, make sure you add 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, check that the Category field header is on the left of the Product field header (if you're grouping rows) or on top of the Product header (if you're grouping columns). Excel uses a shaded line to show you exactly where it plans to place a header as you drag it over the pivot table. If you position the field incorrectly, just click the header and drag it into the proper position.

POWER USERS' CLINIC
Adding Multiple Data Items

As you've seen, you can add more than one field to the pivot table's row or column regions . You can also drag multiple data items into the pivot table. For example, you can show the total for the Quantity field and the average of the Unit Price field by dragging both into the data region in the middle of the pivot table. In this case, the two values appear in different rows, with a label like "Sum of Quantity" and "Average of Unit Price" at the start of the row.

Usually, adding multiple data items just crowds your data. However, it also makes it easy for you to quickly change what information the pivot table shows. In order to understand how to use this technique, it helps to notice that when you add more than one data item to a pivot table, Excel doesn't show the field headers. Instead, it shows a generic field header named Data. If you click the drop-down arrow in the Data field header, Excel shows a list of all the data items it's currently displaying. To hide an item, turn off the checkmark next to its name .

If you decide to show multiple data items, consider using the report format, which separates each data item into a separate column (instead of a separate row). To use report formatting, you need to apply an AutoFormat template, as described on Section 4.3.2.

20.3.2. 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 14.

For example, 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 only want to show the products in a specific category? In this case, the trick is to hide every category you don't want to see.

To hide the products in a specific category, move to that category, and select Data Group and Outline Hide Detail, or just click the Hide Detail icon on the PivotTable toolbar. If you use the Hide Detail command on a category in the orders pivot table, Excel collapses the category down to a single row with the totals for that category.
To expand a group, just select Data Group and Outline Show Detail, or just click the Show Detail icon on the PivotTable toolbar. If you use the Show Detail command on a category in the orders pivot table, Excel shows the product-by-product breakdown for that category.

Note:

Tip: For an even quicker shortcut, 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.

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

Figure 20-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 chart, 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 then adds another layer of grouping to the pivot table, as shown in Figure 20-14.

20.3.3. Filtering Pivot Tables

As you've seen, pivot tables are a miraculously powerful tool for creating detailed summary tables. The only problem is that sometimes these reports are too detailedleaving you with summaries that are nearly as detailed as the original table.

To simplify your pivot tables, you might want to restrict them so that they show only a portion of the total data. This process is known as filtering , and there are two ways you can do it.

The most flexible way to filter is with the fields that you use to group rows and columns. To apply this type of filtering, just click the drop-down arrow at the right of the appropriate field header. Then, turn off the checkmark next to each item you don't want to include in the pivot table. Figure 20-15 shows an example.

Figure 20-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.

Figure 20-15. In this example, filtering is used on the Category field so that only four categories will appear in the pivot table. You can apply this type of filtering on every field that's used to group or subgroup columns or rows.

You can also filter using fields that aren't used to group your pivot table. To do so, you need to add these fields as page fields . Just drag the appropriate field from the PivotTable Field List window to the Drop Page Fields Here region at the top of the worksheet. Once you've added a page field, a drop-down list appears next to the field header. Click the arrow to show the list of all values, and then choose the item you want to display. Figure 20-16 shows an example.

Figure 20-16. In this example, page filtering is used to hide every country except the U.S. by using Ship Country as a page field. Ship Country isn't used as a grouping field, although Ship City is. The resulting pivot table only shows U.S. cities. You could also add the Customer field as a page field so you can show an order summary for specific customers.

Note: There's no reason you can't combine both types of filtering, or filter based on several different fields at the same time.

Page filtering isn't as powerful as group filtering. That's because page filtering lets you choose only a single item for inclusion in the pivot table. When you use filtering on one of the grouping fields, you can show multiple items just by placing a checkmark next to each one.

However, page filtering does provide one interesting feature that group filtering doesn't. You can use your page field to generate multiple pivot tables with a single mouse click, one for each different value. Just follow these steps:

Move anywhere inside the pivot table, and click the PivotTable command on the left side of the PivotTable toolbar.

A pop-up menu appears.
Choose Show Pages.

Excel opens the Show Pages dialog box.
In the Show Pages dialog box, choose the field you want to use.

If you have only one page in your page field area, the Show Pages dialog box doesn't really serve any purpose. If you have multiple page fields, you can choose which field to use for the report.
Click OK to continue.

Excel creates a new worksheet for each different value of the page field, and it creates a clone of your pivot table in each new worksheet.

In other words, if you use Ship Country as your page field, Excel creates a separate worksheet for each different country. Excel names each worksheet using the corresponding page field value (for example, Argentina, Brazil, Canada, and so on). If you switch to the Argentina page, you'll see the same pivot table as you see on the original page, but with one differencethe Ship Country page field is set to Argentina so that the only orders in the table are those from Argentinean customers.

Warning: The Show Pages feature comes in particularly handy if you want to generate a detailed printout for later perusal. But be careful! If you use a page field that has dozens of different values, you'll end up with dozens of worksheets. There's no way to undo the Show Pages command, so you might be stuck painstakingly deleting each worksheet you don't want.

20.3.4. Formatting the Pivot Table

Pivot tables are a little quirky when it comes to formatting. You can use the standard Format Cells command to change cell shading, add borders, and alter fonts. However, these formatting changes don't always stick. When you refresh the data (by selecting Data Refresh Data, or clicking the exclamation mark icon on the PivotTable toolbar), Excel rebuilds the pivot table and attempts to reapply your formatting. You'll quickly find that some of your formatting changes are left out.

To prevent yourself from being disappointed when a routine refresh operation clears away all your hard formatting work, you should stick to using the two formatting features that the pivot table always supports. These include:

Configuring a number format for each data field.
Using AutoFormat (Section 4.3.2) to choose a preset look for the entire pivot table.

You can add other details if you absolutely have to, in order to prepare your data for a presentation or a printout, but don't expect the changes to stay in place permanently.

To format a field with a number format, follow these steps:

Right-click the field header on the worksheet, and choose Field Settings.

The PivotTable Field dialog box appears, where you can change the type of subtotaling calculation used for this field.
Click the Number button.

The familiar Format Cells dialog box appears, but it includes only the Number tab.
Choose a number format from the Category list, and specify a number of digits and a currency symbol, if required.
Click OK.

The PivotTable Field dialog box reappears.
Click OK.

Excel updates the pivot table with the new number format.

Excel also lets you apply a template to format your entire pivot table at once, using one of 21 preset designs. Each template includes settings that determine how borders, headings, subheadings , and cells are formatted. Some use rotated column text and colored cells, and a few even change the structure of your pivot table, transforming all column groups into higher-level row groups.

To format the appearance of the whole pivot table using a template, follow these steps:

Move anywhere inside the pivot table, and click the Format Report button on the PivotTable toolbar.

The AutoFormat dialog box appears, offering a list of formatting choices.
Choose one of the 21 choices, or choose None to reset the formatting.

Each preset format has a unique name. The table templates (Table 1, Table 2, and so on) keep the current orientation. The report templates (Report 1, Report 2, and so on) transform all the column groups into row groups. These templates are most useful if you need a handy way to shrink a pivot table that's too wide to print. Report templates work well if you want to show more than one data item because Excel creates a column for each data item.
Click OK.

Excel updates the PivotTable with the new formatting choices. Figure 20-17 shows an example.

Note:

Tip: Don't waste too much time formatting your pivot charts with fancy labels or colors. Excel will discard most of your customizations the next time you refresh. For example, although chart and axis titles remain in place, it throws away custom fills and colors each time.

20.3.5. Adding a Calculated Field

Using the pivot tables you've learned about so far, you can perform a variety of operations on any field, including averages, sums, and other preset calculations. But what if you want to branch out a little further and create a custom calculation ? You could alter the source data and add a new column containing your calculation, and then recreate the pivot table. But Excel provides a much easier option by letting you define a calculated field.

Figure 20-17. This pivot table uses the Report 6 AutoFormat template. One of the real benefits of formatted pivot tables is that it's easy to see the different groupings and spot the subtotals. It also makes for a much nicer printout. Best of all, these formatting settings stick when you modify the source data and refresh the pivot table.

The following steps show how to create a calculated field within a pivot table. Using these steps, you can modify a pivot table that shows a list of orders so that it totals the amount of revenue generated instead of just adding up the number of units shipped.

Move anywhere inside the pivot table, and click the PivotTable command on the left-side of the PivotTable toolbar.

A pop-up menu appears.
Choose Formulas Calculated Field.

The Insert Calculated Field dialog box appears (as shown in Figure 20-18).
Enter a name for the new field in the Name text box.

In this case, the new name is Revenue.

Enter the formula this field uses in the Formula text box.

(This is a standard Excel formula that modifies one or more of the other fields, which are provided in the Fields list.)

Figure 20-18. Top : Here, a new field named Revenue is created to calculate how much money is being made on any given order. To arrive at the total revenue, the Unit Price field is multiplied by the Quantity field.
Bottom : The worksheet now shows the total revenue by category and customer country.

Your formula can use Excel's built-in functions, or you can enter a formula that alters or combines one or more of the fields that are in the Fields list. For example, the new Revenue field calculates the total revenue generated by multiplying the price of the item by the quantity purchased. It uses the following formula:

 ='Unit Price'* Quantity.

Note that field names that include spaces or special characters , like Unit Price, need to be enclosed in apostrophes . If you're in any doubt, double-click the field name in the list, and Excel inserts it into the Formula text box with apostrophes if they're necessary.

Click OK.

Excel automatically adds the calculated field to the PivotTable Field List window and inserts it in the pivot table as a data item field.

If your pivot table already has other data items, you might want to remove them to simplify your table. Just click the drop-down arrow next to the Data field header. It shows a list of all the data items that are currently displayed. In the Data field's drop-down menu, turn off the checkmark next to the items you don't want to show (like Sum of Quantity), and then click OK.

You can also drag the Data field out of the chart and into the PivotTable Field List window. You can then drag the data field you want to use back to the chart. Even if you don't use your new calculated field, it remains available in the PivotTable Field List for when you need it next.

20.3.6. Creating Pivot Charts

Excel lets you create charts based on the data in a pivot table report. These charts work more or less the same as ordinary Excel charts (covered in Chapter 16), with a few quirks . First of all, Excel always places pivot charts on a separate worksheet. There's no way to place them on an existing worksheet that also contains the source data. Second, all pivot charts start off as stacked column charts. You can change the chart type by right-clicking the chart and choosing Chart Type. However, pivot charts are typically very dense, and many of the specialized chart types don't work well. Figure 20-19 shows a sample pivot chart.

Figure 20-19. In this example, a stacked column chart shows the breakdown of orders by product category and country. Each bar represents a different category. Each bar is subdivided into color -coded sections that represent a country (as detailed in the legend). A pivot chart doesn't necessarily show all the data because you can use filtering to show just a subset of the total information.

Note: Before you create a pivot chart, it's often useful to limit the amount of information in your pivot table. Too much information can lead to a chart that's dense and hard to read. The easiest way to hide data is to avoid using too many levels of grouping, and to restrict groups you aren't interested in, by using filtering, as described earlier.

There are two ways to create a pivot chart:

You can choose the "PivotChart report (with PivotTable report)" option from the first step of the PivotTable and PivotChart Wizard (you saw this screen when you first set up the pivot table).
At any time, you can create a pivot chart for your existing pivot table by clicking anywhere inside it and clicking the Chart Wizard button on the PivotTable toolbar. You can also right-click the pivot table and choose PivotChart.

These two approaches are exactly the same. When you click Chart Wizard on the PivotTable toolbar, Excel doesn't launch the ordinary Chart Wizard. Instead, it creates a new stacked column chart for the pivot table and places it on a new worksheet, without asking for any feedback. It takes the same step if you choose the "PivotChart report (with PivotTable report)" option from the PivotTable and PivotChart Wizard.

Like pivot tables, pivot charts are interactive. If you look closely at a pivot chart, you'll see that field headers you've chosen for rows, columns, and data items appear right on the chart itself. You can use these field headers to change the data that's displayed or apply filtering, and you can configure how subtotals are calculated, all without leaving your chart. Figure 20-20 points out your options.

Figure 20-20. Pivot charts offer all the same options as pivot tables, like letting you rearrange the structure of your summary or apply filtering with a few mouse clicks. In this pivot chart, you can use the Category field header to change which categories are included in your chart, or even change the structure of your pivot table by dragging fields to and from the PivotTable Field List window.

Pivot charts offer all the same options as pivot tables, letting you rearrange the structure of your summary or apply filtering with a few mouse clicks. For example, in Figure 20-20, if you want to show fewer countries, just click the Ship Country field header at the top of the legend on the right side of the chart. A list of countries appears, with a checkmark next to each one you've chosen to display. If you turn off a checkmark, that country disappears from the pivot chart and the underlying pivot table.