Home > Pivot > Layout > Pivot Items Show and Hide Excel Pivot ItemsShow or hide pivot table items, either manually, or with a macro. Download sample file with macros and test data |
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
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.
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
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
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
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
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
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
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
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
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
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
On the pivot table sheet, click the worksheet button, "Include Interns?", and a macro runs
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
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.
Last updated: January 24, 2023 7:50 PM