Analyzing Microsoft Excel 2010 Data with PivotTable Reports

Filtering PivotTable Fields

Filtering a field lets you focus your table on a subset of items in that field. You can filter on the basis of the field’s own content (only the Children and Young Adult categories, for example) or on the basis of values associated with the field (for example, the three categories with the best overall sales). You can apply filters in a variety of ways—one of which is new in Excel 2010.

Filtering with the Report Filter Axis

Figure 23-4, on page 779, shows a PivotTable in which one field, Category, appears on the Report Filter Axis. To create this table, we simply dragged the Category field from the PivotTable Field List to the Report Filter area below the field list. With the table arranged in this manner, we can focus on one category at a time.

When you put a field heading in the Report Filter area, the table initially aggregates all values of the field and displays the heading (All) next to the field name—like this:

To filter the report so that it shows a particular field value, click the drop-down arrow beside the field name, and select a value:

To select two or more field values, select the Select Multiple Items check box. Note, however, that if you filter on multiple field values, the indication beside the field name, above the table, does not tell you which values you’re looking at. It simply says (Multiple Items):

Correcting this deficiency is one of the principal virtues of Excel’s newest PivotTable feature, the slicer.

Filtering with Slicers

A slicer is an independently movable and formattable window containing buttons for each item in a field. You can use the buttons to filter your PivotTable. Figure 23-12 presents an example. Here, we selected three members of the Category field—Children, Romance, and Young Adult. Because these field items appear in a contrasting color in the slicer, you can see at a glance how the table has been filtered.

Figure 23-12 A slicer makes it easy to see how the table has been filtered.

To use a slicer, select any cell in your PivotTable, click Insert Slicer in the Sort & Filter group on the PivotTable Tools Options tab, and then click Insert Slicer. The Insert Slicers dialog box presents a check box for each field in your table. Select a field, and then click OK. You can select multiple fields to create more than one slicer. Figure 23-13 shows a table filtered by two slicers.

Figure 23-13 You can use multiple slicers to filter in more complex ways.

Connecting a Slicer to Multiple PivotTables

A single slicer can slice many tables. If you create multiple PivotTables to show a common set of data in different perspectives, you might find it convenient to set up slicers that are linked to all the related tables. To connect an existing slicer to another PivotTable, select the slicer. Then click PivotTable Connections in the Slicer group on the Slicer Tools Options tab. The PivotTable Connections dialog box displays the name of the selected slicer and a check box for each available PivotTable, as shown here.

Select the tables you want to slice, and click OK.

Formatting Slicers

You can style your slicers by using the same techniques you use to style a PivotTable or an ordinary table. Select a slicer, click the Slicer Tools Options tab, and then take your pick of styles from the Slicer Styles gallery. As with styles elsewhere in Excel, the available choices are keyed to your current workbook theme, so your slicer uses colors that are consistent with the rest of your workbook. For more information about using and customizing built-in styles, see “Formatting Tables” on page 770.

Filtering in the Field List Window

If you’re working with a large external data source and you need only a subset of the data, you can save yourself some time by setting up a filter in the PivotTable Field List window before you execute the query and create your PivotTable. To filter in the PivotTable Field List window, select the heading for the field you want to filter, and then click the arrow to the right of the field heading. The dialog box that appears includes check boxes for each unique item in the selected field:

You can use the check boxes to select one or more particular items in your selected field. If your field is more complex than the example here, you might want to click Label Filters, in response to which Excel presents many additional filtering options:

The options that appear on this menu are tailored for the data type of the selected field. If your field holds dates instead of text, for example, you see these options:

To filter a field on the basis of values associated with that field, click the arrow next to the field heading in the PivotTable Field List window, and then click Value Filters on the menu that appears. For example, to filter the PivotTable in Figure 23-3 so that it shows only the three categories with the highest total sales, click the arrow beside Category, and then click Value Filters to see the following menu.

Then click Top 10 to display the Top 10 Filter dialog box:

Replace the 10 with a 3, and then click OK. Figure 23-14 shows the result.

Figure 23-14 This table has been filtered to show only the three best-selling categories; the rankings are based on values in the Grand Total column.

Note that when you apply a value filter to a field, Excel bases its calculations on the current grand total associated with that field. If you wanted to see the three top-selling categories for the year 2009 (in the example shown in Figure 23-3), you would need to filter the Year field as well as the Category field.