Contextures

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

select multiple items

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

select multiple items

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. go to top

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. go to top

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. go to top

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. go to top

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. go to top

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. go to top

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. go to top

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

remove a subtotal manually

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       

go to top

Download the Sample File

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

More Pivot Table Tutorials

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Pivot Power Premium

 

Last updated: August 19, 2016 1:56 PM
Contextures RSS Feed