Creating Dynamic Lists by Using PivotTables in Microsoft Office Excel 2007

Filtering, Showing, and Hiding PivotTable Data

PivotTables often summarize huge data sets in a relatively small worksheet. The more details you can capture and write to a table, the more flexibility you have in analyzing the data. As an example, consider all the details captured in the following data table.

Each line of the table contains a value representing the Distribution Center, Date, Month, Week, Weekday, Day, and Volume for every day of the year. Each column, in turn, contains numerous values: there are nine distribution centers, data from two years, twelve months in a year, seven weekdays, and as many as five weeks and 31 days in a month. Just as you can filter the data that appears in a table, you can filter the data displayed in a PivotTable by selecting which values you want the PivotTable to include.

To filter a PivotTable based on a field’s contents, click the field’s header in the Choose Fields To Add To Report area of the PivotTable Field List task pane to display a menu of sorting and filtering options.

The PivotTable displays data that’s related to the values with a checked box next to them. Clicking the Select All check box clears it, which enables you to select the check boxes of the values you want to display. Selecting only the Northwest check box, for example, leads to the following PivotTable configuration.

If you’d rather display as much PivotTable data as possible, you can hide the PivotTable Field List task pane and filter the PivotTable by using the filter arrows on the Row Labels and Column Labels headers within the body of the PivotTable. Clicking either of those headers enables you to select a field by which you want to filter; you can then define the filter using the same controls you see when you click a field header in the PivotTable Field List task pane.

Excel 2007 indicates that the PivotTable has filters applied by placing a filter indicator next to the Column Labels or Row Labels header, as appropriate, and the filtered field name in the PivotTable Field List task pane.

So far, all the fields by which you’ve filtered your PivotTable have changed the organization of the data in the PivotTable. Adding some fields to a PivotTable, however, might create unwanted complexity. For example, you might want to filter a PivotTable by weekday, but adding the Weekday field to the body of the PivotTable expands the table unnecessarily.

Instead of adding the Weekday field to the Row Labels or Column Labels area, you can drag the field to the Report Filter area near the bottom of the PivotTable Field List task pane. Doing so leaves the body of the PivotTable in the same position, but adds a new area above the PivotTable in its worksheet.

When you click the down arrow of a field in the Report Filter area, Excel 2007 displays a list of the values in the field. In previous versions of Excel 2007, you could select only one Report Filter value by which to filter a PivotTable; in Excel 2007, selecting the Select Multiple Items check box enables you to filter by more than one value.

Finally, you can filter values in a PivotTable by hiding and collapsing levels of detail within the report. To do that, you click the Hide Detail control (which looks like a box with a minus sign in it) or the Show Detail control (which looks like a box with a plus sign in it) next to a header. For example, you might have your data divided by year; clicking the Show Detail control next to the 2006 year header would display that year’s details. Conversely, clicking the 2007 year header Hide Detail control would hide the individual months’ values and display only the year’s total.

In this exercise, you will focus the data displayed in a PivotTable by creating a filter, by filtering a PivotTable based on the contents of a field in the Report Filters area, and by showing and hiding levels of detail within the body of the PivotTable.

1. On the PivotTable worksheet, click any cell in the PivotTable.

2. In the PivotTable Field List task pane’s Choose fields to add to report section, click the Center field header, click the Center field filter arrow, and then clear the (Select All) check box.

   Excel 2007 clears all the check boxes in the filter menu.

3. Select the Northwest check box and then click OK.

   Excel 2007 filters the PivotTable.

   

4. On the Quick Access Toolbar, click the Undo button.

   

   Excel 2007 removes the filter.

5. In the PivotTable Field List task pane, drag the Weekday field header from the Choose fields to add to report section to the Report Filter area in the Drag fields between areas below section.

6. In the PivotTable Field List task pane, click the Close button.

   

   The PivotTable Field List task pane closes.

7. In the body of the worksheet, click the Weekday filter arrow, and then select the Select Multiple Items check box.

   Excel 2007 adds check boxes beside the items in the Weekday field filter list.

8. Clear the All check box.

   Excel 2007 clears each check box in the list.

9. Select the Tuesday and Thursday check boxes, and then click OK.

   Excel 2007 filters the PivotTable, summarizing only those values from Tuesdays and Thursdays.

10. In cell A5, click the Hide Detail button.

    

    Excel 2007 collapses rows that contain data from the year 2006, leaving only the subtotal row that summarizes that year’s data.