Excel pivot filter: Filtering data in a pivot table

Excel pivot filter: YOUR OBJECTIVE

You generated a pivot table. At this moment you are interested in viewing and understanding various aspects of your data. Perhaps you want to compare certain values; perhaps you want to present particular data without an excess of ‘data clutter’. To accomplish such things, it would be expedient if you could temporarily hide some of the data that is available in your pivot table. The pivot table has filter functions that allow you to reduce or control the amount of visible data in your pivot table.

This learning module “ Excel pivot filter” shows you how to filter pivot-table data so that the data that really interests you is much easier to see and understand.

Excel pivot filter : HOW DOES IT WORK?

In a pivot table report, the Row Labels, Column Labels and Report Filter cells each bear an arrow button next to their respective labels. When clicked, these arrow cause a selection list to be revealed in which all items associated with that column are shown along with a few commmands. Using these selection lists, you can set filters that enable you to specify which data you would like to hide (or reveal). As soon as you’ve specified such a filter, Excel recalculates the data displayed in the pivot table. Any item or items excluded by such filtering do not participate in the new calculation.

Pivot table filters are additive. This means, when you use multiple filters each additional filter further qualifies the data remaining from the previous filter, thus further reducing the amount of displayed data.

Excel pivot filter :Filtering the row and column fields

You can open a filter list by clicking the arrow button associated with the column labels.

 

When you do, a list of all items associated with that column will be revealed. Activated checkboxes indicate the items that will be included in the pivot-table analysis. In the following illustration, for example, you see the open selection list for the column labels.

There you can use your mouse to unselect items that you do not want to appear or be used in the next analysis. Clicking an item removes its checkmark, i.e., unselects it and the checkmark next to (Select All), as well.

When you click  , your new selections will be applied and the pivot-table automatically recalculated.

Excel pivot filter: As you can see, in our present example, the North sales region has been filtered out. This filter not only affects the calculated order values but also changes the customer names that are displayed. That is, customer names associated with the North sales region are no longer visible. After applying a filter, the arrow icon  changes to a filter icon . A filter icon also appears in the PivotTable Field List task pane too, indicating a field to which a filter has been applied.

If you want to filter the row labels, you may proceed just as described above. Since pivot table filters are additive, you can filter any row, even after filtering a column and vice versa.

Excel pivot filter: HINT

If you want to select only a few items for your pivot analysis, you can unselect all items by clicking the (Select All) item in the filter selection list. Then select only those items that you want displayed, i.e., used in the pivot calculation.

Excel pivot filter: Removing filters

If you want to remove a filter entirely, open the selection list by clicking the filter icon . Then, as shown below, click either the button or activate the (Select All) checkbox. Finally, conclude by clicking  .

As you can see in the next illustration, both methods for clearing a filter are also available if you click the filter icon in the PivotTable Field List task pane.

Excel pivot filter: Filtering in the Report Filter

You can also apply a report filter, if you like. To do so, click the arrow icon in the report filter cell. Here too, a selection list with relevant items will appear.

 

Select the item you want from this list by clicking it with your mouse. If you want to select more than one item in the page field, enable the Select Multiple Items option.

In the first table, shown below, you see an unfiltered pivot report that includes all Customer Groups. In the second table, the report filter was used to filter for Customer Group A.

The report filter field also affects which details are displayed in the row and column fields and the calculated values, too. When using this filter, you can also apply a combination of several filters.

Excel pivot filter: Other ways to filter

In addition to the filtering methods discussed above, you can apply custom filters to the columns and rows of your pivot-table report. Both Label and Value Filters are among your choices in the selection lists of the column or row filters.

In particular, the Label Filter allows you to use logical operators to filter certain items. If, as in the example, you want to filter for all customers that have Driving School in their names, you would select the Contains… filter.

 

A dialog box will appear, in which you can enter Driving School.

When you click OK, all customer names that conain the term Driving School will be included in the pivot table, without regard for the exaction position of the term within the customer’s name. When this label filter is applied in the example pivot table, the data will be reduced as shown in the next illustration:

Another way to filter your data is the Value Filter, which allows you to apply filters to the row and/or column totals. For example, you may want to filter for all customers whose orders add up to more than 2,000 dollars. To do that, you would select the Greater Than… option from the Value Filters command.

In this case, you would complete the filter by entering “2000″ into the available input box, and then finish by clicking OK.

 

The applied value filter would have the following effect on the pivot table:

Excel pivot filter: HINT

If entries are cut off because of the filter list’s limited width, you can drag the handle located at the lower-right corner of the selection list to widen or lengthen the displayed list.

Excel pivot filter: HINT

Filters remain in effect even after saving and closing the Excel file. When you reopen the file, all such settings are automatically restored, just as you left them.

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen