Easy Way to Filter Multiple Items in a Pivot Table

Pivot Table Label Filters Tips

How 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.

Pivot Table Label Filters

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.

Label Filters - Advanced Tips

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.

Multiple Filters on a Pivot Field

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

1. label filters
2. value filters
3. manual filters

By default in Excel, you can only use one pivot table filter type at a time, in each pivot field.

• If you apply one type of filter, and then apply a different type of filter, the first pivot filter is automatically removed.
• Excel doesn't give you any warning that the first filter will be removed!

Video: Change a Pivot Table Setting

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.

Pivot Table Option Setting

To change the Pivot Table option, and allow multiple filters, follow these steps:

• Right-click a cell in the pivot table, and click PivotTable Options.
• In the PivotTable Options dialog box, click the Totals & Filters tab
• In the Filters section, add a check mark to 'Allow multiple filters per field.'
• Click the OK button, to apply the setting and close the dialog box.

Easily hide or show pivot table items, with the quick tip in this short Excel video tutorial. The written instructions are below the video

Hide Pivot Items - Long Method

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.

Quick Method to Hide Items

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.

1. Right-click on an item in the Row Labels or Column Labels
2. In the pop-up menu, click Filter, then click Hide Selected Items.
3. The item is immediately hidden in the pivot table.

Quickly Hide All But a Few Items

You can use a similar technique to hide most of the items in the Row Labels or Column Labels.

1. Select the pivot table items that you want to keep visible
2. Right-click on one of the selected items
3. In the pop-up menu, click Filter, then click Keep Only Selected Items.
4. All but the selected items are immediately hidden in the pivot table.

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.

Hide All Arrows

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

Hide Row Arrows

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

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.

Show All Arrows

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

Show Row Arrows

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

Get the Sample File

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.

More Pivot Table Resources

Pivot Table Date Filters

Manually Move Pivot Items

FAQs - Pivot Tables

Pivot Table Introduction

Clear Old Items in Pivot Table

aguilaranonton99.blogspot.com

Source: https://www.contextures.com/excelpivottablelabelfilters.html

Share this post