By default, a Pivot Table processes all records when calculating its values. Data filtering allows you to process only those records that meet your requirements.
To filter against a field, do the following:
Hover over the field header. The filter button () will be highlighted:
Click the filter button to display the filter drop-down list containing field values that can be filtered:
- Deselect field values that must be hidden, and select fields that must be visible. Then, click OK to close the drop-down filter list and apply the filter.
In this manner, you can filter data against multiple fields.
The filter drop-down, invoked for fields arranged into a group, has a tree-like interface which allows you to manipulate the values of all fields in the group via a single drop-down.
To remove filtering against a specific field, invoke the field's filter drop-down list and select (Show All).
You can tell whether a field is being filtered by looking at its header. Filter buttons for these fields are visible even when you're not hovering over the field headers:
Build Complex Filter Criteria
To build complex filter criteria consisting of multiple filter clauses using various filter operators, you can use the Prefilter (Filter Editor):
The Prefilter doesn't affect filtering via filter drop-down lists of fields (see above).
To invoke the Prefilter, click an empty space within the Pivot Table's header region and select Show Prefilter from the context menu:
After building and applying a filter via the Prefilter, the filter panel at the bottom of the control becomes visible. It displays the current filter criteria:
To modify the current filter, open the Prefilter. You can now open it by clicking the Edit Prefilter button.
To temporarily disable the filter, uncheck the button.
To remove filtering, click the button.
See Filter Data via the Filter Editor for information on the filtering capabilities of the Prefilter. To learn how to create filter criteria via the Prefilter, please see Examples of Using the Filter Editor.