Filter data in a pivot table

Product: MyOffice Professional, MyOffice Standard, MyOffice Education

Application: Spreadsheet Desktop version

There are several filtering methods available for pivot tables:

•Using report filters.

•Using row and column filters.

The report filters define a data slice on the basis of which a pivot table is created. Filtering is performed on fields added to the Filters area in the Constructor of the pivot table. The cells for managing report filters are located above the pivot table:

pivot_table_report_filter

Row and column filters are applied to an already generated pivot table when you want to hide or display individual rows/columns depending on the values they contain or according to a specified condition.

When you use the Compact layout, the filter buttons are in cells with common row and column labels. The defaults are Row Labels and Column Labels (you can change the captions for the labels in the Options tab of the pivot table settings pane):

pivot_table_filter_compactview

When you use the Tabular and Outline layouts, the filter buttons are located in the cells of the respective titles:

pivot_table_filter_tableview

You cannot apply multiple filters to a single column or row in a pivot table at the same time. When a new filter is set, the old one is automatically reset.

Report filters and row/column conditional filters work together, but report filters have the highest priority, that is their results are taken into account when setting the filters by condition. The filtering by condition includes the columns and rows that are actually displayed in the pivot table after the report filters are applied.

Setting row and column filters, in turn, affects pivot table functions such as viewing cell details and additional calculations: cell details tables and additional calculations are generated with the filters applied.

Filter data using the report filter

To filter the pivot table data using the report filter, follow these steps:

1.Add the fields that you want to use for filtering to the Filters area in the Constructor of the pivot table.

2.Click the editor_filter_on_drop_active filter button in the row with the desired field at the top of the table.

3.Select the checkboxes next to the values you want to include in the generation of the pivot table.

4.If the list contains a large number of values, use the search bar:

•In the search results, select the checkboxes next to the values you want to display in the column.

•If necessary, repeat the steps to find and select other values.

5.Click Apply.

Once applied, the editor_filter_on_drop_active button in the row with the filter will look like filter_applied .

Filter rows and columns by values

To filter a row or column by values, follow these steps:

1.Click the editor_filter_on_drop_active filter button in the row/column title.

2.If you are using the Compact layout for a pivot table and have added multiple fields to the row or column area, in the Filter window, in the Row/Column drop-down list, select the desired row or column.

3.Make sure that By value filtering option is selected and clear the Select All checkbox.

4.Select the checkboxes next to the values you want to display.

5.If the list contains a large number of values, use the search bar:

•Enter the value you want to find (in full or partially). For example, to find the number 123, you can enter the numbers 12, 23 or 123 in the search bar.

•In the search results, select the checkboxes next to the values you want to display in the column.

•If necessary, repeat the steps to find and select other values.

6.Click Apply.

After the filter is applied, the editor_filter_on_drop_active filter button in the cell with the row/column header will look like filter_applied . When you move the cursor over the button, a tooltip with information about the enabled filters will be displayed.

Filter rows and columns by condition

Two filtering options are available for filtering by condition: by caption (column/row name) and by the values of the calculated data contained in the value area of the pivot table.

To filter a row or column by caption condition, follow these steps:

1.Click the editor_filter_on_drop_active filter button in the row/column title.

3.Select the By condition filtering method.

4.In the Show box, select the Captions option from the drop-down list.

5.In the Condition box, select the type of condition from the drop-down list and in the adjacent box, input the text value for which the condition should be met. In the current version of MyOffice Spreadsheet, regular expressions (“*”, “?” operators) are not supported when inputting a text value.

6.Click Apply.

To filter a row or column by condition for the calculated data values, follow these steps:

1.Click the editor_filter_on_drop_active filter button in the row/column title.

2.If you are using the Compact layout for a pivot table and you have added multiple fields to the row or column area, select the desired field from the drop-down list in the Row/Column field in the Filter window.

3.Select the By condition filtering method.

4.In the Show box, select the Values option from the drop-down list.

5.If more than one field has been added to the value area in the pivot table, select the desired field from the Field drop-down list.

6.In the Condition box, select the type of condition from the drop-down list and in the adjacent box, input the numeric value for which the condition should apply.

7.Click Apply.

Clear the filter

To clear the report filter, follow the steps below:

1.Click the filter_applied button in the row with the filter.

2.In the Filter window, select the Select All checkbox.

3.Click Apply.

To clear the filter for an individual row or column, follow these steps:

1.Click the filter_applied button in the row or column title.

3.Select the current filtering method (the Clear Filter button will be clickable).

4.Click Clear Filter.

To clear all filters applied in the table, follow the steps below:

1.Select one or more cells from the pivot table range.

2.On the Toolbar, in the Pivot Table section, click pivot_table_clear_all_filters .

Was this helpful?

Yes