Contextures

Pivot Table Label Filters

How to apply multiple filters on one Excel pivot field, how to show or hide the filter arrows

Introduction

When you add fields to the pivot table row and column areas, there are built-in filters in the heading cells. Use those drop down lists to show or hide pivot items. With programming, you can show or hide the drop down arrows. Change an option setting to be able to apply multiple filters to a field.

filter drop down arrows

Multiple Filters on a Pivot Field

There are three kinds of filters that you can use in a pivot table:

  • label filters
  • value filters
  • manual filters.

To use more than one filter per field, you can change one of the Pivot Table options. Watch the steps in this short video, and the written instructions are below the video.

To change the Pivot Table option to allow multiple filters:

  1. Right-click a cell in the pivot table, and click PivotTable Options.
  2. Click the Totals & Filters tab Under Filters, add a check mark to 'Allow multiple filters per field.'
  3. Click OK

data source list

Hide Drop Down Arrow to Prevent Filtering

When you add a field to the Row Label or Column Label area of the pivot table, its heading cell shows a drop down arrow. To prevent manual filtering, you can hide the drop down arrows.

The following code will hide the drop down arrow for every field in the first pivot table on the active worksheet.

Sub DisableItemSelection()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
  For Each pf In pt.PivotFields
      pf.EnableItemSelection = False
  Next
End Sub

Instead of hiding all the arrows, you can specify RowFields or ColumnFields. The following code will hide the drop down arrow for every Row field in the first pivot table on the active worksheet. The arrows on Column and Report filters will not be hidden

Sub DisableItemSelectionRows()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
  For Each pf In pt.RowFields
      pf.EnableItemSelection = False
  Next
End Sub

Show Drop Down Arrow to Allow Filtering

If you have turned off the drop down filters, using the code shown above, you can shown the arrows again, by running the following code.

The following code will show the drop down arrow for every field in the first pivot table on the active worksheet.

Sub EnableItemSelection()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
  For Each pf In pt.PivotFields
      pf.EnableItemSelection = True
  Next
End Sub

Instead of showing all the arrows, you can specify RowFields or ColumnFields. The following code will show the drop down arrow for every Row field in the first pivot table on the active worksheet. The arrows on Column and Report filters will not be affected.

Sub DisableItemSelectionRows()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
  For Each pf In pt.RowFields
      pf.EnableItemSelection = True
  Next
End Sub

More Pivot Table Resources

Pivot Table Blog

Manually Move Pivot Items

Pivot Table Article Index

Pivot Table Video Index

FAQs - Pivot Tables

Pivot Table Introduction

Clear Old Items in Pivot Table

Get All the Excel News

For regular Excel news, tips, videos, and special offers, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search Contextures Sites

 

Free Pivot Table Tools

 

 

Peltier Tech Charts for Excel 3.0

 

 

 

Pivot Power Premium

Excel UserForms for Data Entry

 

Last updated: June 14, 2017 11:35 PM
Contextures RSS Feed