Home > Pivot > Filters > Label Filters Pivot Table Label Filters TipsHow to apply multiple filters on one Excel pivot field, how to show or hide the filter arrows. Tip: You can also use Pivot Table Slicers to quickly filter items in the pivot fields. |
When you add fields to the pivot table row and column areas, there are built-in filters in the heading cells. To see the filters, click on the arrow in the heading cell, as shown in the screen shot below.
With the commands in the drop down list, you can apply a Label Filters, or a Value Filter, or use the check boxes to show and hide specific items.
Here are a couple of advanced tips for pivot table filters - most people don't know these tricks!
1) Multiple Filters: By default in Excel, you can only use one type of pivot table filter at a time, in a pivot field. However, there's an advanced filter tip, further down on this page, that shows how you can use one, two or all three filter types at once!
2) Hide Arrows: Another advanced tip is that you can use macros, to hide or show those drop down arrows in the heading cells. By hiding the arrows, you can discourage people from filtering the pivot table, after you have things set up the way you need them! Get the Hide and Show Arrows macros further down on this page.
There are three kinds of pivot table filters that you can use in a pivot field:
By default in Excel, you can only use one pivot table filter type at a time, in each pivot field.
Fortunately, with a simple change to the pivot table option settings, you can use one, two or all three filter types at once! Excel will not remove the previous pivot field filters, when you apply new filters.
This short video shows how to change that filter setting, and the written instructions are below the pivot table filters video tutorial.
To change the Pivot Table option, and allow multiple filters, follow these steps:
Easily hide or show pivot table items, with the quick tip in this short Excel video tutorial. The written instructions are below the video
In an Excel pivot table, you might want to hide one or more of the items in a Row field or Column field.
To do that, you could click the drop down arrow for the Row or Column Labels, to see the list of pivot items in that pivot field. Then, in the list, remove the check mark for items you want to remove.
For example, to hide the data for 7-Feb-10, you’d click on the check mark to remove it.
Note: After you apply a filter, the filtering arrow changes, to show a filter icon. If you point to the arrow, a pop-up tooltip shows the filter settings that were applied.
Instead of searching through a long list of items in a drop down list, you can use a quick command to hide the selected items.
You can use a similar technique to hide most of the items in the Row Labels or Column Labels.
When you add a field to the Row Label or Column Label area of the pivot table report, its heading cell shows a drop down arrow.
To prevent manual filtering, you can use one of the following macros to hide the drop down arrows in the field headers, as shown in the screen shot below.
The following code will hide the drop down arrow for every field in the first pivot table on the active worksheet.
Sub HideArrowsALL() 'hide drop down arrow for every field in 'first pivot table on active sheet 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 HideArrowsRows() 'hide drop down arrow for every ROW field 'in first pivot table on active sheet 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
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 ShowArrowsALL() 'show drop down arrow for every field in 'first pivot table on active sheet 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 ShowArrowsRows() 'show drop down arrow for every ROW field 'in first pivot table on active sheet 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
Click here to get the Pivot Table Label Filters sample file for this tutorial. The zipped file is in xlsm format, and contains the macros shown on this page.
Last updated: March 22, 2023 12:35 PM