Apply Multiple Filters on a Pivot Field

Instead of looking at all the data in a pivot table, you can use filters to narrow your focus. The most noticeable ones are the Report Filters at the top of the pivot table. You can use those to select one item, or multiple items, to show in the results.

In the screen shot below, the Report Filter has been set to show only New York City. In the Row area, the Product and OrderMth fields have been added, and Total Price is in the Values area.

multiplefilterspivot02

Filter the Row Fields

You can use filters in the Row and Column fields too. There are 3 types of field filters:

  • Label
  • Value
  • Manual

We’ll see how they work individually, and then how they can be used together.

Apply a Label Filter

In this example, the pivot table has data from 2013-01 to 2013-12. To show only the last six months of the year, you can use a Label Filter on the Order month field.

  • Click the arrow in the OrderMth heading
  • Point to Label Filters
  • Click Greater Than
  • In the Label Filter window, type 2013-06 in the second box, and click OK

multiplefilterspivot03

Now, only the data from July to December is visible.

multiplefilterspivot04

Apply a Value Filter

Next, we’ll apply a value filter on the OrderMth field, to show the top 2 months for each product. To do this, we’ll apply a Top 10 filter on the field.

  • Click the arrow in the OrderMth heading
  • Point to Value Filters
  • Click Top 10
  • In the Top 10 Filter window, type 2 in the middle box, and click OK

multiplefilterspivot05

The pivot table now shows the 2 months with the highest sales, but the Label filter was removed. In the screen shot below, month 2013-05 is included in the Bran results. For Chocolate Chip, both months are in the first half of the year.

multiplefilterspivot06

So, when you add a different type of row filter, the first filter is removed.

Add a Manual Filter

Finally, we’ll try a Manual Filter. For this, you add or remove check marks in the list of pivot items for the field.

multiplefilterspivot07

Again, as soon as a new filter is applied, the old filter is removed. Now only the sales from the first 3 months are shown.

multiplefilterspivot08

Change the Pivot Table Filter Options

By default, a pivot table is set up to allow only one filter per field, as we saw in the examples above. However, if you want to use more than one filter per field, you can change one of the Pivot Table options.

  • Right-click any cell in the pivot table, and click PivotTable Options.
  • Click the Totals & Filters tab
  • Under Filters, add a check mark to ‘Allow multiple filters per field.’
  • Click OK

multiplefilterspivot09

Now you can apply both a Label filter and a Value filter to the OrderMth field, and both will be retained. In the screen shot below, both the Label filter (Greater Than 2013-06) and the Value filter (Top 2) have been applied, and both are in effect, as you can see in the popup message.

NOTE: You’re limited to one of each filter type per pivot field.

multiplefilterspivot10

Use PivotPower Premium Add-In

If you’ve bought a copy of my PivotPower Premium add-in, you can quickly turn the “Allow Multiple Filters” setting on and off with a command on the toolbar.

  • On the PivotPower tab, in the Filters group, click Filters
  • Click Allow Multiple Filters – On, or Allow Multiple Filters – Off

multiplefilterspivotpower

It’s also one of the Default Settings that you can store, so it will be automatically set when you use the Apply Defaults command.

multiplefilterspivotpower2

Watch the Pivot Table Filters Video Tutorial

To see the steps for using multiple filters on the same pivot field, please watch this short Excel video tutorial. It shows the steps in Excel 2010

____________

This entry was posted in Sort and Filter. Bookmark the permalink.

2 Responses to Apply Multiple Filters on a Pivot Field

  1. Pingback: Excel Roundup 20140127 | Contextures Blog

  2. Pingback: 10 Ways Excel Pivot Tables Can Increase Your Productivity - BRAD EDGAR

Leave a Reply

Your email address will not be published. Required fields are marked *