How to filter a pivot table in Excel

Pivot tables make great reports in Microsoft Excel, but adding a filter or two can make them more flexible. Here’s how.

table and calculator

Photo: utah778, Getty Images/iStockphoto

Pivot tables in Microsoft Excel are a great way to organize and analyze data, and the more you know about the feature, the more you can benefit from it. For example, pivot table filtering is a great way to focus on specific information, and you’ll often notice that this capability is added to dashboards. Fortunately, pivot table filtering is easy, and in this article I’ll show you two ways to do it.

I’m using Microsoft 365, but you can use previous versions. You can download the .xlsx demo file or work with your own data. The browser version fully supports the PivotTable tool.

We see: How to add a dropdown list to an Excel cell (TechRepublic)

This article assumes that you know how to create a basic pivot table, but it also provides instructions for creating a sample pivot table. If you need help with the basics, you may want to read How to Use the PivotTable in Excel to Turn Data into Useful Information before continuing.

pivot table in excel

We will need a pivot table before we start filtering, and to this end we will build the pivot table shown in Figure A, based on the data described in the same paper. To do this, click anywhere within the dataset and do the following:

  1. Click the Insert tab, and then click the PivotTable in the Tables group.
  2. In the resulting dialog, click the Current worksheet option so you can see the data and the pivot table at the same time and enter F1 (Figure B) as a site.
  3. Click OK, and Excel will display a pivot table window and a list of fields.
  4. Utilization Figure C As a guide, construct the pivot table shown in Figure A.

Figure A

excelptfilter-a.jpg

Build this pivot table.

Figure B

excelptfilter-b.jpg

Figure C

excelptfilter-c.jpg

Building a pivot table.

This simple pivot table shows each person’s daily amounts, adding sums that share the same date. Since there is a date, Excel automatically adds date components, such as month, quarter, and year. I kept the default month. The order of the data in the data set does matter little. A pivot table is a good report, as is but you may want to focus on specific information.

How to use AutoFilter in Excel

Once the pivot table is created, you can immediately start filtering with what is already there. This dropdown in the row labels cell is an autofilter, similar to the filter you’d use in a normal dataset. Click on it and you will see several options, which you are probably already familiar with. There are a number of built-in filters, such as contains, does not contain, equals, and so on. In this case, uncheck “Select All” and select “James” to see only “James” records (figure d).

figure d

excelptfilter-d.jpg

Filter all records except those of James.

You can also collapse Luke and Martha’s areas by clicking [-] Icon to the left of their name. The difference is that their names and total amounts will still be shown. You just remove its details.

Using the Row Labels dropdown menu, you can also make a file Search Filter by entering a full or partial value. For example, let’s use this feature to display all individuals who have the letter “A” in their name:

  1. Expand the entire pivot table if necessary.
  2. In the search control, enter the letter A (Figure E).
  3. Click OK.

Figure E

excelptfilter-e.jpg

Use the search filter to find records when you need more flexibility.

In this simple and contrived example, it is hard to imagine conducting such a search. But once you get to grips with a lot of different data and search elements, you’ll be glad to know that this feature is there. Also note that the filter items at the bottom of the dialog are set automatically for you. It is also important to note that this filtering time does not change the structure of the pivot table. This will not be true in the next section.

These types of searches are great for a quick check, but they aren’t easy to use. If others are playing around with the pivot table, you’ll need to add more intuitive filters.

How to add a filter to the interface in Excel

The dropdown menu and search filters are good for you, but not so good for others who may be viewing the information in your pivot table. Fortunately, you can add a filter control to the interface. For clarity, let’s add a region filter as follows:

  1. First, fully expand the pivot table, if necessary.
  2. Click inside the pivot table to display the list of fields. If it doesn’t pop up, right-click the pivot table and choose Show Field List from the bottom of the resulting submenu.
  3. In the Fields list, drag the region from the top to the Filters area (shape and). Excel will add a filter on top of the pivot table. From the dropdown menu, choose Northeast, and watch for the pivot table to update accordingly.

shape and

excelptfilter-f.jpg

Use the search filter to find records when you need more flexibility.

You can drag any field in a Pivot table to area filters. for example shape g Displays data filtered by individuals, specifically James for the Northeast. Note, however, that doing this removes personal details from the actual pivot table. James has $1,163 for the month of May in the Northeast. If you like, you can take a moment to change the filters and watch the resulting pivot table change.

shape g

excelptfilter-g.jpg

You can have multiple filters.

see also