Excel -- Pivot Tables -- Show and Hide Items

  1. Show All Items -- Excel 2002
  2. Hide Items -- Excel 2002
  3. Show All Items -- Excel 97/2000
  4. Hide Items -- Excel 97/2000
  5. Show Items in Specific Field -- Excel 97/2000
  6. Hide Items in Specific Field -- Excel 97/2000
  7. Show Specific Item in Specific Field
  8. Hide Page Field Items
  9. Show Specific PivotChart Items
  10. Hide Specific PivotChart Items  
  11. Hide Pivot Subtotals
Download the zipped sample file
Table of Contents 

Show All Items -- Excel 2002

Each field button has an arrow that opens a dropdown list of items.

  1. In Excel 2002, the first checkbox, except in a Page Field, is Show All.
  2. Add a check mark to this checkbox, to display all the items.

Hide Items -- Excel 2002

You can hide all except one of the items in a field.

  1. Remove the check mark from the Show All checkbox, to remove all the check marks in the list
  2. Check at least one of the items, then click OK.

 

Show All Items -- Excel 97/Excel 2000

In previous versions of Excel, where there is no Show All checkbox, you can use programming to show or hide multiple fields. The following code will show all items in all row fields.

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

Sub PivotShowItemAllVisible()
'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 Items -- Excel 97/Excel 2000

In previous versions of Excel, where there is no Show All checkbox, you can use programming to show or hide multiple fields. The following code hides all items, except the last item, in all row fields.

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

Sub HidePivotItemsVisible()
'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 -- Excel 97/Excel 2000

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

Sub PivotShowItemsField()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String
Set pt = ActiveSheet.PivotTables(1)
strPF = InputBox("What Field?", "Field Name")
Set pf = pt.PivotFields(strPF)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
   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 -- Excel 97/Excel 2000

The following code will prompt you for a field name, and will hide all items in the specified field. 

Sub PivtoHideItemsField()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String
Set pt = ActiveSheet.PivotTables(1)
strPF = InputBox("What Field?", "Field Name")
Set pf = pt.PivotFields(strPF)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
   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

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. 

Sub PivotShowSpecificItems()
 
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

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
On Error Resume Next
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

 

 

Hide Page Field Items

A Page Field dropdown doesn't have checkboxes that allow you to select multiple items. You can select All, or an individual item to display.

Use the following technique to display multiple items from a Page field.

  1. Double-click the Page field button, to display the PivotTable Field dialog box.
  2. From the Hide items list, select the items you want to hide.
  3. Click OK.

Or

  1. Drag the Page field button to the Row area.
  2. Use the check boxes to select or deselect items
  3. Drag the Page field button back to the Page area

The Page Field displays Multiple Items, and the Pivot table only shows results for the Page items that are not hidden.

 

Show Specific PivotChart Items

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

Sub PivotChartShowItemsField()
Dim ch As Chart
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String
Set ch = ActiveChart
strPF = InputBox("What Field?", "Field Name")
Set pf = ch.PivotLayout.PivotFields(strPF)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
    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 PivotChart Items

The following code will prompt you for a field name, and will hide all items in the specified field. 

Sub PivotChartHideItemsField()
Dim ch As Chart
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String
Set ch = ActiveChart
strPF = InputBox("What Field?", "Field Name")
Set pf = ch.PivotLayout.PivotFields(strPF)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
   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. The following code removes all subtotals from the pivot table. 

Sub NoSubtotals()
'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
  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
Next pt

End Sub

 

 
       
1. Pivot Tables -- Dynamic Data Source
2. Pivot Tables -- Data Field Layout
3. Pivot Tables -- Show and Hide Items
4. Pivot Tables -- Clear Old Items
5. Pivot Tables -- Field Settings
6. Pivot Tables -- GetPivotData
7. Pivot Tables -- Grouping Data
8. Pivot Tables -- Multiple Consolidation Ranges
9. Pivot Tables -- Printing   
10. Pivot Tables -- Custom Calculations 
11. Pivot Tables -- Pivot Cache     
12. Pivot Tables -- Protection      
13. Pivot Tables -- Grand Totals 		 


Table of Contents 
Dec 20, 2007

  

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 18, 2008 11:45 PM