Filtering Fields

Last modified: June 26, 2023

You can use various types of filtering to display only required data in a pivot table. In addition, you can create custom filters based on your needs.

To filter values of any field, click Filter on the field header. In the pop-up window that opens, select only values you want to display and click OK. After that, the Filter icon will be highlighted with purple and the data will be filtered according to the selected criteria.

For example, to filter payment amount values to display only those that were made in 2005, click Filter on the header of the payment date (Year) field and clear 2006 in the pop-up window.

You can add a filter field to filter all data in the pivot table. From the Data Source view, drag a required field to the filter area in the pivot table. The screenshot below displays that the film was added to filter the list of films by their rating.

Creating a custom filter

In addition, you can create custom filters using the Prefilter dialog to apply advanced filtering conditions.

To create a custom filtering condition

1. On the Pivot Table document view toolbar, click Prefilter. Alternatively, right-click the pivot table header and select Prefilter on the shortcut menu.

2. In the PivotGrid Prefilter dialog that opens, click to add a new condition.

3. Click the field name and select a required one from the drop-down list.

4. Click the condition criteria operator and select a required one from the drop-down list.

5. To apply the filter, click Apply and then click OK.

The pivot table will display data matching the created filtering conditions. As you can see, the list of film titles was filtered to see it starting with CHILL LUCK film title and ending with DANCING FEVER one.

Adding multiple conditions to the same group

In the PivotGrid Prefilter dialog, you can add as many conditions as you need to the same group.

To add more conditions to the same group, click , specify the condition properties, and then click OK.

To remove a condition, click Delete next to the corresponding condition or navigate to the condition and press DELETE.

Adding a new group of conditions

In the PivotGrid Prefilter dialog, you can add a new group of conditions.

To add a new group of conditions, click the upper-level group operator and select Add Group on the menu. Then, specify the condition properties and click OK.

To remove a group, click its logical operator and select Remove Group on the menu.

Editing conditions

At the bottom of the Pivot Table document view, you can do the following:

Edit the conditions: Click Edit Prefilter. In the PivotGrid Prefilter dialog that opens, make the changes and click OK.
See the data unfiltered: Clear the check box next to the filtering condition.
Cancel filtering: Click Cancel.