Pivot Table Filter in Excel

Once you have a Pivot Table report to summarise your data, you will want to add filters so that the user can slice and dice the Pivot Tables and see only the information they require. 

In this complete guide on how to filter Pivot Table data in Excel, we'll cover the different types of Pivot Table filters, how to apply them, and even share a few of our favorite tricks along the way.

Download the sample workbook used in this guide to follow along.

Different Types of Pivot Table Filters

There are three types of Pivot Table filters in Excel; the Field filter, the Report filter and the Pivot Table Slicer. All three are covered using practical examples in this article, but here is a quick overview of each.

Field filter: This is used to filter the labels or values of a Pivot Table. It looks extremely similar to the AutoFilter that is applied to tables and ranges in Excel. You can show only specific rows of a Pivot Table by unchecking the items you want hidden or by specifying criteria such as greater than 500.

Report filter: A filter that appears above a Pivot Table and enables the filtering of the Pivot Table items by the value in another field. For example, a Pivot Table showing the total sales by product can be filtered by a specified region using a ‘Region’ field in the report filter. 

Slicer: A very powerful modern filter that has many advantages over the other filter types. It can be used to easily filter multiple Pivot Tables and Pivot Charts that you may have arranged on an Excel dashboard.

Using the Pivot Table Field Filter

The following image shows a simple Pivot Table that presents the total sales by product name. Using the field filter, we can filter out any products that we do not want to see in the report.

Let’s say that we want to remove the ‘coffee’ and ‘juice’ products as we do not need to report on sales of beverages.

1. Click on the filter arrow in the first cell, or header of the first column, of the Pivot Table.
2. Uncheck the boxes for Coffee and Juice.
3. Click OK.

The Pivot Table no longer displays the results of these two products and the filter icon is shown in the header of the first column. This indicates to others that a filter is applied and they are not looking at the full list of products.

Now, what if there are multiple fields being used in the Rows area of a Pivot Table? In the following Pivot Table, both the ‘Region’ and ‘Product’ fields are in the Rows area.

The Pivot Table is in Compact Form report layout, which means that both sets of labels are shown in the same column, and therefore, there is only one filter arrow for both fields.

Let’s see how we can apply a Pivot Table field filter to either the ‘Region’ or ‘Product’ labels.

1. Click the filter arrow in the header of the column.
2. Use the Select field list to specify the field that you want to filter by. The list of items at the bottom of the filter will change depending upon the selected field.

If the Pivot Table is displayed in the Tabular Form or Outline Form report layout, then the different labels of the Rows area are shown in separate columns and will each have a filter arrow in their column header. This makes it a more straightforward task to filter the Pivot Table.

Pivot Table Filter by Values

It may seem like it is not possible to perform a Pivot Table filter by cell values as there is no filter arrow that appears on the values column. However, it is of course, absolutely possible.

The same filter arrow that is used for the Row labels can also be used to filter Pivot Tables by value.

Let’s filter the Pivot Table to show only the products where the total sales is greater than or equal to £1,000.

1. Click the filter arrow in the header of the field you want to filter.
2. Click Value Filters > Greater Than Or Equal To.

3. Enter 1000 in the box provided and click OK. 

Only the four products that meet the filter values criteria are shown.

To remove the filter field from the Pivot Table, for both labels and values, click the filter arrow for the filter you want to remove and click the Clear Filter From option.

Using the Report Filter in Excel Pivot Tables

The report filter provides an easy way to set an Excel Pivot Table filter based on a cell value. When a field is added to the report filter, a simple drop-down list appears above the Pivot Table for users to make their selection.

In the following Pivot Table, the ‘Region’ field has been added to the Filter area of the Pivot Table. The report filter appears a couple of rows above the Pivot Table. Cell B1 in this instance.

When the report filter arrow is clicked, a list of items is shown similar to the report filter, so that you can easily make the required selection. Clicking the Select Multiple Items checkbox provides checkboxes by all item names, making it possible to select multiple items in the Pivot Table filter.

You can add as many fields as you need to the report filter. In the following Pivot Table, the ‘Customer’ field has also been added. A filter for ‘Ireland’ has been applied to the ‘Region’ filter, but the ‘Customer’ is set to ‘All’.

Filter Pivot Table Data with a Slicer

Excel Slicers were introduced back in Excel 2010 to offer a superior filter experience to the classic Report Filter. The Report Filter is still very useful and takes up less space on a spreadsheet, but Slicers offer numerous other advantages.

• They can be connected to multiple Pivot Tables allowing us to filter multiple Pivot Tables and Pivot Charts with one click of a button.
• They can be styled however we desire.
• A Slicer can be moved anywhere on the worksheet and offers more formatting options including its size, if a header should be included or not, and whether the list of items is displayed vertically or horizontally.
• They work great for touch screen devices allowing a great user experience on mobile.
• Slicers are dependent on each other, so a filter on one Slicer affects the items displayed on another.

To insert a Slicer:

1. Click on the Pivot Table.
2. Click PivotTable Analyze > Insert Slicer.
3. In the Insert Slicers window, check the box for the field(s) you want to use in a Slicer and click OK.

In this example, the ‘Region’ field has been added as a Slicer. You can add as many Slicers as you want, but in this example, we will use just one.

In the following image, the Slicer has been used to filter the Pivot Table for sales in the region of ‘Germany’ only. Multiple regions can be selected by holding the Ctrl key as you click the Slicer items.

The red x in the top right of the Slicer is the Clear Filter button and will restore the Slicer to all items selected.

There is much more to learn about Slicers. You can customise them how you desire and there are some useful settings to modify. 

Connect a Slicer to Multiple PivotTables

Probably the greatest advantage offered by Slicers, is the ability to filter multiple Pivot Tables at once. To do this, we must first connect the Slicer to the Pivot Tables we want it to filter.

In the following example, there are two Pivot Tables, one for total sales by product and another for total sales by customer. Both Pivot Tables have been sorted in descending order by value.

A Slicer for the ‘Region’ field is added and the Columns setting on the Slicer tab of the Ribbon has been set to 4. This neat trick has displayed the Slicer items horizontally instead of vertically.

This Slicer is currently only connected to the first Pivot Table, because that was the selected Pivot Table when the Slicer was inserted.

To connect the Slicer to the second Pivot Table;

1. Click on the Slicer.
2. Click Slicer > Report Connections from the Ribbon.

In the Report Connections window, check the box for the SalesByCustomer Pivot Table so that it filters that Pivot also. Click OK.

You can see the Pivot Tables from all sheets of the provided practice workbook. It is possible to connect a Slicer to these other Pivot Tables, and they will be filtered despite being on different worksheets.

In the following image, the Slicer is used to filter both Pivot Tables to show the sales from the region of the ‘UK’ only. Notice how the order of the rows in the SalesByProduct Pivot Table also changed as a result of the filter.

If you insert a new Pivot Table after inserting a Slicer, it is automatically connected to the Slicer. So conversely, you may need to visit Report Connections to disconnect a Slicer.

Clear All Pivot Table Filters

It is easy to clear all Pivot Table filters. In fact as easy as one click (or maybe two).

The following Pivot Table has two filters applied. The Pivot Table values are filtered to only show products where total sales are greater than or equal to £1,000. And there is a report filter applied to show results for the region of ‘India’ only.

To clear all filters at once;

1. Click in the Pivot Table.
2. Click the Clear button in the Sort & Filter group of the Data tab on the Ribbon.

Want to master Excel today?

Learn it all and master Excel by enrolling in a structured online Excel course that teaches you everything you need to know. Practice alongside a Microsoft MVP and learn the latest skills to boost your workplace efficiency today!
 

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial