Creating Dynamic Worksheets by Using PivotTables in Microsoft Excel 2010

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 a table in which each row 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, 12 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 an Excel table or other data collection, 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. When you do, Excel displays a menu of sorting and filtering options.

The PivotTable displays several sorting options, commands for different categories of filters, and a list of items that appear in the field you want to filter. Every list item has a check box next to it. Items with a check mark in the box are currently displayed in the PivotTable, and items without a check mark are hidden.

The first entry at the top of the item list is the Select All check box. The Select All check box can have one of three states: displaying a check mark, displaying a black square, or empty. If the Select All check box contains a check mark, then the PivotTable displays every item in the list. If the Select All check box is empty, then no filter items are selected. Finally, if the Select All check box contains a black square, it means that some, but not all, of the items in the list are displayed. Selecting only the Northwest check box, for example, leads to a PivotTable configuration in which only the data for the Northwest center is displayed.

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 can filter; you can then define the filter by using the same controls you see when you click a field header in the PivotTable Field List task pane.

Excel indicates that a 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 we’ve filtered the 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 unchanged, but adds a new area above the PivotTable in its worksheet.

When you click the filter arrow of a field in the Report Filter area, Excel displays a list of the values in the field. When you click the filter arrow, you can choose to filter by one value at a time. If you’d like to filter your PivotTable by more than one value, you can do so by selecting the Select Multiple Items check box.

If your PivotTable has more than one field in the Row Labels area, 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 2009 year header would display that year’s details. Conversely, clicking the 2010 year header’s Hide Detail control would hide the individual months’ values and display only the year’s total.

Excel 2010 provides two new ways for you to filter PivotTables: search filters and Slicers. With a search filter, you can type in a series of characters for Excel to filter that field’s values. To create a search filter, click a field’s filter arrow and type the character string for which you want to search in the filter menu’s Search box.

For example, if the PivotTable’s Center field contains the values Atlantic, Central, Midwest, Mountain West, North Central, Northeast, Northwest, Southeast, and Southwest, typing the character string “No” limits the values to North Central, Northeast, and Northwest.

In versions of Excel prior to Excel 2010, the only visual indication that you had applied a filter to a field was the indicator added to a field’s filter arrow. The indicator told users that there was an active filter applied to that field but provided no information on which values were displayed and which were hidden. In Excel 2010, Slicers provide a visual indication of which items are currently displayed or hidden in a PivotTable.

To create a Slicer, click any cell in a PivotTable and then, on the Options contextual tab of the ribbon, in the Sort & Filter group, click Insert Slicer to display the Insert Slicers dialog box.

Select the check box next to the fields for which you want to create a Slicer, and click OK. When you do, Excel 2010 displays a Slicer for each field you identified.

A Slicer displays the values within the PivotTable field you identified. Any value displayed in color (or gray if you select a gray-and-white color scheme) appears within the PivotTable. Values displayed in light gray or white do not appear in the PivotTable.

Clicking an item in a Slicer changes that item’s state—if a value is currently displayed in a PivotTable, clicking it hides it. If it’s hidden, clicking its value in the Slicer displays it in the PivotTable. As with other objects in an Excel 2010 workbook, you can use the Shift and Ctrl keys to help define your selections. For example, suppose you create a Slicer for the Month field while every month is displayed.

If you want to hide every month except January, February, and March, you click the January item to hide every month except January. Then hold down the Shift key and click March to have Excel 2010 display just the data for the months of January, February, and March. You can then add another month, such as July, to the filter by holding down the Ctrl key and clicking July in the Slicer.

To use a Slicer to remove a filter, click the Clear Filter button in the upper-right corner of the Slicer. If you want to resize a Slicer, you can do so by dragging the resize handle in the lower-right corner of the Slicer. To hide the Slicer, right-click it and then click the menu command that starts with the word “Remove.” For example, the Month field’s menu command would be Remove Month.

In this exercise, you’ll 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, by showing and hiding levels of detail within the body of the PivotTable, by using the Search box, and by using Slicers.

Set Up

You need the Focusing_start workbook located in your Chapter09 practice file folder to complete this exercise. Open the Focusing_start workbook, and save it as Focusing. Then follow the steps.

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

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

   Excel clears all the check boxes in the filter menu.

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

   Excel filters the PivotTable.

   

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

   

   Excel removes the filter.

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

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, if necessary, select the Select Multiple Items check box.

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

8. Clear the All check box.

   Excel clears each check box in the list.

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

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

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

    

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

    

11. In cell A5, click the Show Detail button.

    

    Excel redisplays the collapsed rows.

12. On the ribbon, click the Options contextual tab, and then, in the Show group, click Field List.

    

    The PivotTable Field List task pane opens.

13. In the PivotTable Field List task pane, click the Month field header arrow.

    The filter menu opens.

14. In the Search box, type Ju.

    Excel displays the months June and July in the filter list.

    

15. Click OK.

    Excel applies the filter.

16. On the Options contextual tab of the ribbon, in the Actions group, click the Clear button, and then click Clear Filters.

    

    Excel clears all filters from the PivotTable.

17. On the Options contextual tab of the ribbon, in the Sort & Filter group, click Insert Slicer.

    

    The Insert Slicers dialog box opens.

18. In the Insert Slicers dialog box, select the Center check box, and then click OK.

    A Slicer for the Center field appears.

19. Click the Atlantic item.

    Excel filters the PivotTable so only results for the Atlantic center appear.

20. In the Slicer, click Midwest, and then, while holding down the Ctrl key, click Mountain West and then Northwest.

    Excel filters the PivotTable so it displays results for the Midwest, Mountain West, and Northwest centers.

    

21. In the upper-right corner of the Slicer, click the Clear Filter button.

    

    Excel removes the filter from the Center field.

22. Right-click the Slicer, and then click Remove “Center”.

    Excel closes the Slicer.

    Clean Up

    

    Save the Focusing workbook, and then close it.