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 either in the PivotTable Field List window or on the PivotTable. 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 filtering in the PivotTable Field List window before you execute the query and create the table.

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 will 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 15–3 so it shows only the three categories with the highest total sales, click the arrow beside Category, and then click Value Filters. In the Value Filters menu:

you would click Top 10, which would take you to the Top 10 Filter dialog box:

where you replace the 10 with a 3 and then click OK. Figure 15–12 shows the result.

Figure 15–12: We filtered the table to show only the three best-selling categories.

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 we wanted to see the three top-selling categories for the year 2005 (in the example shown in Figure 15–3), we would need to filter the Year field as well as the Category field.