Filtering

Top  Previous  Next

There are 2 types of filters you can apply to a pivot table.

 

 

Prefilter criteria

 

Prefilters are filters that are applied to the entire dataset before the pivot table is displayed.  To apply a prefilter criteria, right-click on a blank area in the table header to bring up the context menu:

 

pivot_filtering_01

 

 

Select the Show Prefilter Dialog item from the context menu.

 

pivot_filtering_02

 

 

In the Filter builder form, you can add the filter conditions you require.  In the example below, the filter we use will only display data for 2017.

 

pivot_filtering_03

 

 

Once added, the prefilter conditions will be displayed on a panel at the bottom of the pivot table.

 

pivot_filtering_04

 

You can then add more conditions, or delete the existing conditions using the buttons on this panel.

 

 

Column filters

 

You can also apply filters on the columns displayed in the pivot table, by clicking on the filter button on the column header.

 

pivot_filtering_05

 

 

Select one or more values to filter the column on, and the pivot table will update the display accordingly.

 

 

When to use prefilters and column filters

 

Prefilters have a higher visibility and supports more complex filtering conditions.  The prefilter panel displays what filters are currently being applied, and you can easily recall previously used filters easily.

 

Column filters are useful for ad-hoc filtering.  However, you must remember which column filters are currently being applied, as they are not indicated in the pivot table.