Search Contextures Sites

Excel Pivot Table Tutorial -- 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 for this Excel pivot table tutorial

  

 

 

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()
'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 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()
'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 -- Excel 97 and Excel 2000

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

Sub PivotShowItemsField()
'pivot table tutorial by contextures.com
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()
'pivot table tutorial by contextures.com
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()
'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

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()
'pivot table tutorial by contextures.com
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()
'pivot table tutorial by contextures.com
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()
'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
  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       

 

 

 

Download the zipped sample file for this pivot table tutorial

Pivot Table Tutorials

Pivot Table Introduction 
Clear Old Items in Pivot Table
Create a Pivot Table in Excel 2007 
Custom Calculations 
Data Field Layout
Dynamic Data Source
FAQs - Pivot Tables
Field Settings
Filter Source Data  
Filters, Top 10 
Filters, Report Filters
GetPivotData Function
Grand Totals
Grouping Data
Layout, Excel 2007
Multiple Consolidation Ranges
Pivot Cache   
PivotTable Style
Printing a Pivot Table   
Protection  
Running Totals  
Show and Hide Items 
Sorting
Subtotals 
Summary Functions
Unique Item Count

Pivot Table Books

Beginning Pivot Tables (Excel 2007) 
Pivot Tables Recipe Book (Excel 2003) 
Pivot Tables Recipe Book (Excel 2007) 

Pivot Table Add-Ins

Pivot Power 
Pivot Play PLUS 

Pivot Table Videos

Clear Old Items
Copy a Custom PivotTable Style
Create Pivot Table in Excel 2007
Create Pivot Table from Multiple Sheets
Data Field Layout
Date Filters, Add
GetPivotData
Group Data
Layout, Excel 2007
Report Filters, Add
Running Totals
Select Sections
Subtotals, Create Multiple
Top 10 Filters

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2013
All rights reserved.

 

Last updated: June 3, 2013 6:57 PM