Use the Pivot Table Report Filter for even better pivot tables

In this lesson, we’ll look at how to use the Pivot Table Report Filter. This is one of the areas available in the Field Layout section of the Pivot Table Fields pane. We covered the other areas in an earlier lesson (How to change the layout of your pivot tables – opens in a new window) but the Report Filter deserves a lesson all of its own.

What the Pivot Table Report Filter does

The Pivot Table Report Filter allows you to create a pivot table and then use one or more fields from your raw data as filters on the pivot table. Some examples of how you could use Report Filters include:

• Show sales by item, and use Salesperson as a filter, so you can generate a separate report of sales by each salesperson.
• Show sales by sales person broken down by week, and have a filter for Item Sold. Changing the filter changes the pivot table to show only the item(s) selected in the filter.

We’ll work through the first of these examples as part of this lesson (the second example is similar to the first in terms of the steps you’d need to take).

You can download a copy of the spreadsheet we used to create this lesson from the Worked Examples section to the right.

Using the Pivot Table Report Filter

Using the Pivot Table Report Filter is easy – you simply drag one or more fields into the Report Filter area, as shown in this example:

When you do this, the Pivot Table changes to look like this:

Example – Sales by Item, filtered by Salesperson

In this example, we’lll filter by Salesperson, so we can generate a report of sales by item.

• Change the Report Layout so it looks like this:
  
• Your pivot table will change to look like this:
  
• The filter defaults to (All) but you can now filter the table below by clicking the  button. You’ll then see this:
  
• In this case, I have clicked on Abdul, but I haven’t yet clicked OK to apply the filter.
• Rather than clicking one item (Abdul), then clicking OK, I could do a search. This is useful if my list of salesperson is very long. But even in this case it might have some use. In the example below, I’ve typed the letter M into the search box, and the list has been filtered accordingly to only those people whose names start with M:
  
• Another option would be to check the Select Multiple Items option, so you can include more than one item in the filter, as shown here where I’ve cleared the seach and then selected Leila and Mike:
  
• The pivot table will be updated when we click OK. Based on that last example, the Report Filter field now looks as follows:
  
• Note that the Filter icon has changed to  to indicate that the list is filtered. The filter field has also changed to show what the list is filtered by. Note that if we’d chosen just one name, that name would be shown instead of it saying Multiple Items.

Multiple fields in the Report Filter

At the start of the lesson, we showed an example of a Report Filter with two fields in the Report Filter – Salesperson, and Sales. This would allow us to filter first by Salesperson, and then drill further into the sales for the selected salesperson (or salespeople) to refine the pivot table report even further:

Another example – which you could try for yourself – would be to set up a Report Filter that includes Salesperson and Item, and then filter to show a report of the sales, by day, of Televisions that Mike has made.

Summary

This lesson showed you how to use the Report Filter in your Excel Pivot tables. Using the Report Filter allows you to create a report where you can easily filter out data that you don’t want to see, or to quickly produce multiple versions of the same report (e.g. sales by salespeople.).

Lesson Subject – Pivot Tables, Pivot Tables
Related Lessons – 100, 129, 128