Section 21.5. Filtering a Pivot Table | Excel 2007[c] The Missing Manual

21.5. Filtering a Pivot Table

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 may 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: report filtering and group filtering.

Note: As you already know, you can create a pivot table that draws its data from a structured table (the kind you learned to create in Chapter 14). Structured tables support filtering, but their filtering settings have no effect on linked pivot tables. In other words, even if you apply a filter to hide some of the raw data in your source table, all the data still appears in the pivot table. If you want to filter information from the pivot table, you need to use pivot table filtering.

21.5.1. Report Filtering

Report filtering lets you filter out your raw data so that your pivot table uses only the rows that really interest you. Maybe you want to narrow down your sales summary so it focuses on the sales made in a specific country. To do so, you need to drag the appropriate field (in this case, Ship Country), to the Report Filter box in the PivotTable Field List pane. You'll see it appear on your worksheet, as shown in Figure 21-19.

Note: The name "report filtering" stems from the fact that pivot tables are sometimes called "pivot table reports."

Once you've added a field to the Report Filter box, it's available for filtering. To actually set the filter, click the drop-down arrow in the field box. This action pops up a list with all the values that are currently in that field. You can then choose the one value you want to display. Figure 21-20 shows an example.

Figure 21-19. The report filter fields appear just above the pivot table. If you have more than one report filter, Excel gives each one a separate row.

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

If you want, you can filter for several values at once. To do so, switch on the Select Multiple Fields checkbox at the bottom of the list. When you do, a checkbox appears next to every entry in the list, with a checkmark. Excel uses checked values to create the pivot table, and ignores unchecked entries. Figure 21-21 shows an example.

Figure 21-21. Clear the checkbox next to items you don't want to see in your pivot table. In this example, the pivot table is using the sales records from three specific days.

Report filtering doesn't let you create the more advanced filter conditions that you used with tables (Section 14.2.5.1). You can't pick dates that occur in a set time period, numbers that fall in a specific range, and so on. For that, you need group filtering, which is described next.

21.5.2. Group Filtering

Group filtering works with the fields that you use to group your data into rows and columns . It gives you another way to home in on the exact subset of data that interests you.

Excel applies group filtering in a slightly different way than ordinary report filtering. When you use report filtering, Excel pares down your data before it creates the pivot table. But when you use group filtering, Excel reads all the raw data, builds the complete table, and then hides certain groups, based on your filter criteria. In other words, report filtering filters the raw data, while group filtering filters the grouped data. So which one's better? It all depends on exactly what you're trying to accomplish. Imagine you're creating a pivot table that analyzes a decade 's worth of chocolate sales, grouped by chocolate type (white chocolate, dark chocolate, and so on). You may choose to ignore small purchasesfor that, you'd use ordinary report filtering. Additionally, you may want to ignore product types that didn't pull in many sales (like bacon-wrapped chocolate). After all, they'll just clutter up your pivot table. To hide these categories, you use group filtering.

To apply group filtering, just click the drop-down arrow at the right of the Column Labels or Row Labels cell . Figure 21-22 shows exactly where to go.

Figure 21-22. To filter by Category, you need to click the drop-down arrow next to the Row Labels cell. Figure 21-23 shows the drop-down list that appears.

You can add filter settings to more than one field. You can hide certain category and certain products. However, you need to configure them separately. Here's how:

Click the drop-down arrow to show the filter list (see Figure 21-23) .
In the "Select field" box at the top, choose Category .
Now uncheck the categories you don't want to see, and then click OK to make it official .
Click the drop-down arrow to show the filter list again .
Now, choose Product in the "Select field" box .
Hide the products you don't want to see (by clearing the checkboxes) and then click OK .

Figure 21-23. Because there are two row fields (Category and Product), you need to start by choosing the one you want to use for your filter. Then, hide the items you don't want to see by clearing the checkbox next to them in the list. In this example, only five categories appear in the pivot table. Excel also adjusts the subtotals and grand totals accordingly .

The drop-down filter menu has additional options that let you filter out groups according to more sophisticated criteria. You could use these options to show or hide categories (or products) that contain specific text, start or end with a certain letter, and so on. To set these options, click the drop-down arrow next to the Column Labels or Row Labels cell, pick your field, and then choose an option from the Label Filters submenu. For example, to show only categories that begin with a "C", choose Label Filters Begins With, and then type C into the dialog box that appears.

Note: The filtering options in the Label Filters submenu work the same way as the filtering options for structured tables (tables you create with the Insert

Tables

Table command). To learn more about your filtering options, refer to Section 14.2.5.1.

Less Than, and then fill in the number 500 when asked. Once again, youve seen this sort of filter beforewhen you created tables in Chapter 14.

Filtering can be a bit tricky at first, particularly if you have several row fields or several column fields to play with. Before you set a label or value filter, make sure you pick the right field. The Value Filters Less Than command has a different effect depending on whether you apply it to the Product field or the Category field.

If you apply it to the Product field, you'll see slow-selling products that sell less than 500 units. If you apply it to the Category field, you'll see only categories that have less than 500 units of sales across all their products. In the current example, this action would hide every category, because every category has sold significantly more than 500 units.

Note: If you apply a filter on a row field (like Products), your column fields have no effect. Thus, in this example, it doesn't matter that you've subdivided your product sales by country. When evaluating the filter condition, Excel considers the total sales for that product.

You can remove your filters using the Clear Filter command in the filter menu. However, if you've added filters to more than one field, you need to remove each filter separately. A faster alternative is to choose PivotTable Tools Options Actions Clear Clear Filters. Doing so removes every filter and returns your pivot table back to normal, so it shows all the data.