Thank you for subscribing to the RSS feed for Excel Pivot Tables .
For more pivot table tips, please visit Contextures.com
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.
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
Now, only the data from July to December is visible.
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
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.
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.
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.
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
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.
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
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.
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
____________
Share and Enjoy
• Facebook • Twitter • Google Plus • LinkedIn