How to collapse and expand specific items in a pivot field, or the entire pivot field. The Expand and Collapse commands do not affect items that have been filtered
To see the steps for expanding and collapsing pivot table details, please watch this short video.
You can show or hide the the expand and collapse buttons in a pivot table. These are the small + / - signs at the left side of a pivot item heading.
To show or hide the the collapse and expand buttons:
You can expand or collapse a specific item in a pivot field, and see only its heading. The other items in that field will not be affected.
To expand or collapse a specific item:
The Collapse and Expand buttons (or double-click) affect all occurrences of a pivot item. In the screen shot below, there are multiple instances of the Bars category, and they all are collapsed. You can't change collapse Bars in one Region only.
NOTE: Items in the innermost pivot field do not have an expand/collapse button. If you double-click on an innermost pivot field item:
Instead of changing pivot items individually, you can use the pivot table commands, to expand or collapse the entire pivot field.
Instead of changing pivot items individually, you can use the pivot table commands, to expand or collapse the details to a specific level.
Unfortunately, there is no command that will expand or collapse the entire pivot table. You can use two steps to expand everthing in the Row Labels area, and the same two steps for the Column Labels area.
In the Row Labels area:
Repeat these two steps for the Column Labels area, if necessary.
Use this macro to prevent a specific pivot item from expanding in a pivot table. Modify the code, to use the pivot field and pivot item names in your pivot table.
In this code sample, the field name is "Category" and the "Crackers" pivot item cannot be expanded, because the code prevents that from happening. Clicking on the Expand button does not show the products in that category.
NOTE: This technique does NOT secure the Crackers data. People could work around this restriction in many ways
Add this code to the pivot table's worksheet code module. Modify the code, to use the pivot field and pivot item names in your pivot table
Private Sub Worksheet_PivotTableUpdate _ (ByVal Target As PivotTable) Dim pt As PivotTable Dim myPT As String Dim myPF As String Dim myPI As String myPT = "ptSalesCat" myPF = "Category" myPI = "Crackers" Set pt = ActiveSheet.PivotTables(myPT) If Target.Name = pt.Name Then Application.EnableEvents = False With pt.PivotFields(myPF) _ .PivotItems(myPI) If .ShowDetail = True Then Application.ScreenUpdating = False .ShowDetail = False End If End With End If Application.ScreenUpdating = True Application.EnableEvents = True End Sub
No Macros: Click here to download the Expand and Collapse sample file. The file is zipped, and in xlsx format, with no macros.
Macro to Keep Collapsed: Click here to download the file with the Keep One Pivot Item Collapsed macro. The Excel file is zipped, and in xlsm format, and contains macros.
Last updated: April 23, 2021 11:58 AM