Search Contextures Sites
Custom Search

 

More Tutorials

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

 

 

 

 

 

Pivot Table Label Filters

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

Multiple Filters on a Pivot Field

Video: Multiple Filters on a Pivot Field

Hide Drop Down Arrow to Prevent Filtering

Show Drop Down Arrow to Allow Filtering

More Pivot Table Tutorials

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.

data source list

Multiple Filters on a Pivot Field

There are three kinds of filters that you can use in a pivot table -- label filters, value filters and manual filters. To use more than one filter per field, you can change one of the Pivot Table options.

    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

Video: Multiple Filters on a Pivot Field

To see the steps for applying multiple filters on a pivot field, please watch this short video tutorial.

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.

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.