Filtering and Sorting an Excel 2010 Pivot Table
3 of 9 in Series: The Essentials of Excel 2010 Pivot Tables and Pivot Charts
You can filter and sort the data in an Excel 2010 pivot table to display a subset of data arranged the way you want to view it. Excel automatically adds drop-down filter arrows to the Report Filter field as well as the labels for the Column and Row fields. These filter arrows enable you to filter out all but certain entries in any of these fields, and in the case of the Column and Row fields, to sort their entries in the table.
If you've added more than one Column or Row field to your pivot table, Excel adds collapse buttons (-) that you can use to temporarily hide subtotal values for a particular secondary field. After clicking a collapse button in the table, it immediately becomes an expand button (+) that you can click to redisplay the subtotals for that one secondary field.
Filter a pivot table report
Perhaps the most important filter buttons in a pivot table are the ones added to the Report Filter field(s). By selecting a particular option on the drop-down lists attached to one of these filter buttons, only the summary data for that subset you select is then displayed in the pivot table itself.
When you filter a pivot table field, Excel replaces the standard drop-down button icon with a cone-shaped filter icon, indicating that the field is currently being filtered to show only some of the values in the data source.
Filtering individual Column and Row fields
The filter buttons attached to the Column and Row field labels let you filter out entries for particular groups and, in some cases, individual entries in the data source. To filter the summary data in the columns or rows of a pivot table, follow these steps:
Click the Column or Row field's filter button.
Deselect the check box for the (Select All) option at the top of the list box in the drop-down list.
Click the check boxes for all the groups or individual entries whose summed values you still want displayed in the pivot table.
As with filtering a Report Filter field, Excel replaces the standard drop-down button icon for that Column or Report field with a cone-shaped filter icon, indicating that the field is currently being filtered and only some of its summary values are now displayed in the pivot table.Pivot table after filtering two fields in the table.
To redisplay all the values for a filtered Column or Report field, you need to click its filter button and then click (Select All) at the top of its drop-down list. Then click OK.
Sort a pivot table
You can instantly reorder the summary values in a pivot table by sorting the table on one or more of its Column or Row fields. To sort a pivot table, follow these steps:
Click the filter button for the Column or Row field you want to sort.
Click either Sort A to Z or Sort Z to A at the top of the field's drop-down list.
Click the Sort A to Z option when you want the table reordered by sorting the labels in the selected field alphabetically, from the smallest to largest numeric value, or from the oldest to newest date. Click the Sort Z to A option when you want the table reordered by sorting the labels in reverse alphabetical order (Z to A), values from the highest to smallest, and dates from the newest to oldest.