Microsoft Office Tutorials and References
In Depth Information
Sorting and Filtering Pivot Table Data
Filtering the report
Perhaps the most important filter buttons in a pivot table are the ones added
to the field(s) designated as the pivot table FILTERS. 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 displays in the pivot table.
For example, in the sample pivot table (refer to Figure 9-5) that uses the
Gender field from the Employee Data list as the Report Filter field, you can
display the sum of just the men’s or women’s salaries by department and
location in the body of the pivot table doing either of the following:
✓ Click the Gender field’s filter button and then click M on the drop-down
list before you click OK to see only the totals of the men’s salaries by
✓ Click the Gender field’s filter button and then click F on the drop-down
list before you click OK to see only the totals of the women’s salaries by
When you later want to redisplay the summary of the salaries for all the
employees, you then re-select the (All) option on the Gender field’s
dropdown filter list before you click OK.
When you filter the Gender Report Filter field in this manner, Excel then
displays M or F in the Gender Report Filter field instead of the default (All). The
program also replaces the standard drop-down button with a cone-shaped
filter icon, indicating that the field is filtered and showing only some of the
values in the data source.
Filtering column and row fields
The filter buttons on the column and row fields attached to their labels enable
you to 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, click the column or row field’s filter button and start by clicking the
check box for the (Select All) option at the top of the drop-down list to clear this
box of its check mark. Then, click the check boxes for all the groups or individual
entries whose summed values you still want displayed in the pivot table to put
back check marks in each of their check boxes. Then click OK.