Home > Pivot > Layout > Pivot Items Show and Hide Excel Pivot Items Show or hide pivot table items, either manually, or with a macro. Download sample file with macros and test data

Show and Hide Pivot Items Manually

Each field button has an arrow that opens a drop down list of items. To switch between Single Selection mode and Multiple Selection mode, click in the Select Multiple Items.checkbox, at the bottom of the list

Show Pivot Items

1. In Single Selection mode, click on any item, to see just that item, or click (All), to see all the items.

Hide Pivot Items

You can hide pivot items, but at least one pivot item must remain visible. You can't do a "Hide All", either manually, or with a macro.

1. In Multiple Selection mode, click on any check mark, to clear a check box, and hide that item.
2. Or, to show only a few items in a long list:
   • Remove the check mark from the (All) checkbox, to remove all the check marks in the list
   • Then, check at least one of the items in the list
   • Click OK

Show All Items - Macro

Instead of selecting pivot items manually, you can use a macro to show or hide multiple fields. The following code will show all items in all row fields, in ALL pivot tables on the active sheet.

To show column fields, change pt.RowFields to pt.ColumnFields.

To show all fields, change pt.RowFields to pt.VisibleFields.

Sub PivotShowItemAllVisible()
'pivot table tutorial by contextures.com
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next

For Each pt In ActiveSheet.PivotTables
   For Each pf In pt.RowFields
    pf.AutoSort xlManual, pf.SourceName
     For Each pi In pf.PivotItems
         pi.Visible = True
     Next pi
    pf.AutoSort xlAscending, pf.SourceName
   Next pf
Next pt

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub       

Hide All Items Except Last - Macro

Instead of hiding pivot items manually, you can use a macro to show or hide multiple items. Excel will not allow you to hide all the items though -- at least one pivot item must be visible.

The following code hides all items, except the last item, in all row fields, in ALL pivot tables on the active sheet.

To hide column fields, change pt.RowFields to pt.ColumnFields.

Sub HidePivotItemsVisible()
'pivot table tutorial by contextures.com
'hide all pivot items in all tables on sheet
'except last item
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next

For Each pt In ActiveSheet.PivotTables
   For Each pf In pt.RowFields
    pf.AutoSort xlManual, pf.SourceName
    For Each pi In pf.PivotItems
        pi.Visible = False
    Next
  Next
  pf.AutoSort xlAscending, pf.SourceName
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub 

Show Items in Specific Field - Macro

The following code will prompt you for a pivot field name, and will show all items in the specified pivot field. It affects the first pivot table on the active sheet.

Sub PivotShowItemsField()
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String
On Error Resume Next

Set pt = ActiveSheet.PivotTables(1)
strPF = InputBox("What Field?", "Field Name")
Set pf = pt.PivotFields(strPF)
Application.ScreenUpdating = False
Application.DisplayAlerts = False

   With pf
    .AutoSort xlManual, .SourceName
     For Each pi In .PivotItems
         pi.Visible = True
     Next pi
    .AutoSort xlAscending, .SourceName
    End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub       

Hide Items in Specific Field - Macro

The following code will prompt you for a field name, and will hide all items (except the last one) in the specified field. It affects the first pivot table on the active sheet.

Sub PivtoHideItemsField()
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String
On Error Resume Next

Set pt = ActiveSheet.PivotTables(1)
strPF = InputBox("What Field?", "Field Name")
Set pf = pt.PivotFields(strPF)
Application.ScreenUpdating = False
Application.DisplayAlerts = False

   With pf
    .AutoSort xlManual, .SourceName
     For Each pi In pf.PivotItems
         pi.Visible = False
     Next pi
    .AutoSort xlAscending, .SourceName
    End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub 

Show Specific Item in Specific Field - Macro

Thanks to Jeff Bloomer, who contributed this code.

The following code will prompt you for a field name and item name, and will hide all other items in the field. It affects the first pivot table on the active sheet.

Sub PivotShowSpecificItems()
'pivot table tutorial by Jeff Bloomer
'posted on contextures.com
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strPromptPF As String
Dim strPromptPI As String
Dim strPF As String
Dim strPI As String
On Error Resume Next

strPromptPF = "Please enter the name of the field you wish to filter."
strPromptPI = "Please enter the item you wish to filter for."
 
Set pt = ActiveSheet.PivotTables(1)
strPF = InputBox(strPromptPF, "Enter Field Name")
strPI = InputBox(strPromptPI, "Enter Item")
Set pf = pt.PivotFields(strPF)
Application.ScreenUpdating = False
Application.DisplayAlerts = False

With pf
    .AutoSort xlManual, .SourceName
    For Each pi In pf.PivotItems
        pi.Visible = False
    Next pi
    .PivotItems(strPI).Visible = True
    .AutoSort xlAscending, .SourceName
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub       

Show Specific Pivot Chart Items - Macro

The following code will prompt you for a field name, and will show all items in the specified field, in the active pivot chart.

Sub PivotChartShowItemsField()
'pivot table tutorial by contextures.com
Dim ch As Chart
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String
On Error Resume Next

Set ch = ActiveChart
strPF = InputBox("What Field?", "Field Name")
Set pf = ch.PivotLayout.PivotFields(strPF)
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    pf.AutoSort xlManual, pf.SourceName
     For Each pi In pf.PivotItems
         pi.Visible = True
     Next pi
    pf.AutoSort xlAscending, pf.SourceName

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub       

Hide Specific Pivot Chart Items - Macro

The following code will prompt you for a field name, and will hide all items (except the last one) in the specified field.

Sub PivotChartHideItemsField()
'pivot table tutorial by contextures.com
Dim ch As Chart
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String
On Error Resume Next

Set ch = ActiveChart
strPF = InputBox("What Field?", "Field Name")
Set pf = ch.PivotLayout.PivotFields(strPF)
Application.ScreenUpdating = False
Application.DisplayAlerts = False

   With pf
    .AutoSort xlManual, .SourceName
     For Each pi In pf.PivotItems
         pi.Visible = False
     Next pi
    .AutoSort xlAscending, .SourceName
    End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub 

Hide Pivot Subtotals

When you add more than one field to the row or column area, the pivot table will display subtotals. You can remove the subtotals manually or with a macro

To manually remove a subtotal from a field:

1. Right-click on in item in that pivot field.
2. In the popup menu, click on Subtotal [field name], to remove the check mark

Macro to Remove All Subtotals

The following code removes ALL subtotals from ALL pivot tables on the active sheet.

Sub NoSubtotals()
'pivot table tutorial by contextures.com
'turns off subtotals in pivot table
'.PivotFields could be changed to
'.RowFields or .ColumnFields
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next

For Each pt In ActiveSheet.PivotTables
  pt.ManualUpdate = True
  For Each pf In pt.PivotFields
    'First, set index 1 (Automatic) to True,
    'so all other values are set to False
    pf.Subtotals(1) = True
    pf.Subtotals(1) = False
  Next pf
  pt.ManualUpdate = False
Next pt

End Sub       

Hide Specific Item Data - Macro

In this example, click a button above the pivot table, to show or hide data from specific food sales records. A Slicer on a different sheet is updated, to hide one specific item in a pivot field

• Each sales record shows the salary level of the sales rep -- Levels are from 1 to 10, and "Intern".
• A Slicer on another sheet is connected to the pivot table, and filters the RepLevel field.
• The RepLevel field does not need to be in the pivot table layout

On the pivot table sheet, click the worksheet button, "Include Interns?", and a macro runs

• A message asks if you want to include "Intern" level data.
• Click Yes or No, to update the pivot table.
  • Yes - Slicer is updated, to clear any filter on the RepLevel field items
  • No - Slicer filter is cleared, then "Intern" is filtered out

Sample Code for Macro

Here is the code for the worksheet button macro - InternHideShow.

You can also download the Hide Item With Slicer sample file, to get the sample data and the macro code.

Sub InternShowHide()
Dim wb As Workbook
Dim wsP As Worksheet
Dim scLevel As SlicerCache
Dim siLevel As SlicerItem
Dim mySI As Variant
Dim strInt As String

On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False

Set wb = ThisWorkbook
Set wsP = wb.Sheets("SalesPivot")
strInt = "Intern"
mySI = MsgBox("Show Interns?", _
  vbQuestion + vbYesNo, "Show Interns?")

Set scLevel = wb.SlicerCaches("Slicer_RepLevel")
scLevel.ClearManualFilter

Select Case mySI
  Case vbYes
    'show all levels
    scLevel.ClearManualFilter
  Case vbNo
    'clear slicer filter, then hide Intern
    scLevel.ClearManualFilter
    For Each siLevel In scLevel.VisibleSlicerItems
      If siLevel.Name = strInt Then
        Set siLevel = scLevel.SlicerItems(siLevel.Name)
        siLevel.Selected = False
        Exit For
      End If
    Next siLevel
  Case Else
    'do nothing
End Select

exitHandler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub

errHandler:
    MsgBox "Could not update pivot table"
    Resume exitHandler

End Sub

Download the Sample File

Pivot Item Macros: Download the zipped sample file for this Excel pivot table tutorial. The file is in xlsm format and contains the macros from this page, except for the InternHideShow macro. Remember to enable macros when you open the file, if you want to test the macros.

Hide Item With Slicer: Download the zipped Hide Intern Data sample file from the Hide Specific Item example above. The file is in xlsm format and sample data and the InternHideShow macro. Remember to enable macros when you open the file, if you want to test the worksheet button and macro.

More Pivot Table Tutorials

Pivot Item Macros

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table