Filtering Fields

You can use various types of filtering to display only required data in a pivot table.

To filter values of any field, click the Filter button on the field header and in the dialog box that appears select only required values that you want to display. The Filter button 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, you should click the Filter button on the header of the payment date (Year) field and clear the 2006 checkbox in the dialog box.

You can add the filter field to filter the whole data in the pivot table. Drag-and-drop a required field from the Data Source view to the filter area in the pivot table. In the picture below the film rating film was added to filter the list of films by their rating.

Also, you can use the PivotGrid Prefilter dialog box to apply advanced filtering conditions to filter data. To open the dialog box, click the Prefilter icon on the toolbar or right-click the pivot table header and select Prefilter on the shortcut menu.

To create a filtering condition in the Prefilter dialog box, do the following steps:

  1. Click the Add button to add a new condition.
  2. Click the field name and select a required one from the drop-down list.

  3. Click the condition criteria operator (displayed using the green font color) and select a required one from the drop-down list.

  4. To add more conditions to this group, click the Add button again and specify the condition properties. To remove a condition, click the condition button or navigate to the condition, and press DELETE.

  5. 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. To remove a group, click its logical operator and select Remove Group on the menu.

  6. The pivot table will display data matching the created filtering conditions. In the case described here, the list of film titles was filtered to see it starting with CHILL LUCK film title and ending with DANCING FEVER one. To edit the conditions, click the Edit Prefilter button. To see the data unfiltered, clear the checkbox next to the filtering condition. To cancel filtering, click the Remove button.