More Than One Filter on Pivot Table Field
One of the best features of a pivot pivot table is filtering, which allow you to see specific results. You can:
- Add Report Filters at the top of the pivot table, to limit what's summarized in the pivot table data.
- Use Label, Value, and Manual filters on the pivot fields, to narrow the focus.
The pivot field filters are easy to use, and you can quickly change the pivot table report for different needs. However, if you apply a Value filter on a pivot field, then try to add a Label filter, the first filter is removed.
It's possible to apply multiple pivot field filters at the same time, so keep reading to learn how.
Apply a Label Filter
In this example, the pivot table has data from January 2008 to June 2010. To compare the first six months of each year, you can use a Label Filter on the month number field.
Next, you'd like to see the months with the highest costs, so you apply a Top 10 filter on the same field. The pivot table now shows the 3 months with the highest costs, but the Label filter was removed. In the screen shot below, months 7, 11 and 12 are included in the results.
Change the Pivot Table Filter Options
To use more than one filter per field, you can change one of the Pivot Table options.
- Right-click a 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 Month number field, and both will be retained.
Include a Manual Filter
In addition to a Label filter and a Value filter, you can also apply a Manual filter to the same pivot field. To do that, click the filter drop down, and add or remove check marks in the list of pivot items.
You're limited to one of each filter type per pivot field, and in the pivot table shown below, the month number field now has a Manual filter, Label filter and Value filter.
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.