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.

pivotfiltersmulit01

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.

pivotfiltersmulit03

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

pivotfiltersmulit04

Now you can apply both a Label filter and a Value filter to the Month number field, and both will be retained.

pivotfiltersmulit05

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.

pivotfiltersmulit06

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.

pivotfiltersmulit07

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.

____________

16 comments to More Than One Filter on Pivot Table Field

  • Which Excel version are you using and does multiple filters work in Excel 2003?

  • Gregory, the example was created in Excel 2010, and the multiple filters feature is also available in Excel 2007, but not in earlier versions

  • António Ferreira

    2007 lost FILTER funcionality in Pivot Table?

    In 2003, i could make TOP10 (or bottom) filter in value fields, BUT ALSO on LABEL fields.

    Per example: if i have a YEAR_DAY (1-365) Field, with values to it (Sales, for example), i could make a filter to TOP7 YEAR_DAY, that would show me everyday, the sales and sum of the last 7 days...
    Now, i can choose the TOP7 VALUE days, i can't choose to (daily) refresh the data of the LAST7 days...

    How is it possible to let this feature die???

    youtube downloader

  • Cindy K

    In the Excel 2010, the report filter shows the entire list to choose from. Whereas in Excel 2007, it is limited to only what is selected either one filter or multiple filter. How can I limit the list in the 2010 version in the report filter?

  • @Cindy K, the report filter behave the same in Excel 2010 as they did in Excel 2007 -- the full list of items is shown in each filter, even if other filters have been applied.
    You could try Slicers in Excel 2010 – they show applicable items at the top of the list, and other items are below.

  • Cindy K

    @Debra. Thanks for the reply. Maybe I'm referencing the wrong Excel... Excel 2003... only shows the list of selected items in the report filter. I created a bunch of reports, only changing the report filter so that is user specific. Now, it appears that they access to all the data, even though they had it before, but it wasn't apparent.

  • Peter G

    I would like to add more than one filter on the labels. Is it realy no possible whithout writing a vba code ?

  • Tony Stevens

    I have a problem with an Excel 2003 workbook, with multiple worksheets, each containing a pivot table, in which I have selected multiple page field items (after checking the relevant checkbox), to display a subset of over 1000 customers.

    This all works fine, until the pivot table is refreshed (automatically on open) and if this results in new items being added to the relevant page field, these are automatically included in the filter.

    Any ideas please?

  • Tony Stevens

    That's great, thanks.

  • Susan Wagner

    I want to sort by 2 report filters - first a larger group and then narrow it down by the second report filter. I understand in 2010 it would be done with the slice function, but how can i do it in excel 2007. The data is first a functional area and the second filter should be role within that functional area. If i put both at the top it doesn't work as the filters are not dependent on one another.

  • Christine

    Is there not a way to have it group month, years - so 2 different year but same month can be side by side in the column view?

  • Larry

    I have two cells (Region & Sales Rep) I want to use as the reference for filtering my Pivot Table. How can I use the code below to do this? I don't know enough about VBA to code a 2nd filter reference.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'This line stops the worksheet updating on every change, it only updates when cell
    'H6 or H7 is touched
    If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub

    'Set the Variables to be used
    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As String

    'Here you amend to suit your data
    Set pt = Worksheets("Sheet1").PivotTables("PivotTable1")
    Set Field = pt.PivotFields("Category")
    NewCat = Worksheets("Sheet1").Range("H6").Value

    'This updates and refreshes the PIVOT table
    With pt
    Field.ClearAllFilters
    Field.CurrentPage = NewCat
    pt.RefreshTable
    End With

    End Sub

    • Jeff Weir

      Hi Larry. Probably better to post this question at a help forum. If you post it at http://www.chandoo.org/forum I can keep an eye out for it. Otherwise there might be quite a bit of back and forth in this thread by the time any change works as intended.

      One thing I would suggest is have a think about whether it's better to connect a Slicer to the pivot (assuming you have Excel 2010 or later) and use that instead, as then there's no VBA required.

  • Jojo

    I am using Excel 2013. Even after I check mark to 'Allow multiple filters per field.', filter button is appearing in the first field only(the filter buttons are not appearing in the second field onwards as shown above). help me please to get filter button in all the fields in the pivot table.

  • Max

    How can I even add filter(drop down arrow) in the first place? Like the drop down arrow in your example ( MthNum ). The drop down arrow only appears in the first column ( Row Label ).

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>