Someone asked me if it’s possible to prevent one item in a pivot table from being expanded, to show its details. They wanted all the other items to be expandable though. There’s nothing built in that will allow this, so I had to use programming instead. This macro keeps one pivot item collapsed in a pivot table, but doesn’t affect any other pivot items. Modify the code, to use the pivot field and pivot item names in your pivot table.
Expand and Collapse Pivot Fields
Before we look at the macro, you might want to watch this short video. It shows a few different ways that you can manually expand and collapse the pivot items in an Excel pivot table.
Why Keep One Pivot Item Collapsed?
The person who asked for this “keep one pivot item collapsed” feature didn’t say why it was needed.
So here are a few possible reasons that you’d want to do this:
- The category has hundreds of items, and there’s never any need to show all of them
- The pivot table will be printed in a report, and you don’t want readers to see details for that category
- There’s private information in that category, which you don’t want people using the pivot table to see
In this example, the macro will keep the Crackers item collapsed, in the Category field.
Pivot Table Privacy
The macro shown below will make sure that the details for the Crackers field will stay hidden in the pivot table. That will help with the first 2 reasons in the list above.
However, hiding things in the pivot table won’t ensure that the hidden information remains private. There are many ways to work around pivot table protection and pivot table restrictions. So, keep that in mind, if you use this macro.
For example, if you double-click on the cell that shows the total Quantity for Crackers, the underlying records will be sent to a new sheet in the workbook, showing all the “hidden” data.
Macro to Keep One Pivot Item Collapsed
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.
- First, add this code to the pivot table’s worksheet code module in your workbook.
- Then, modify the variable settings, to use the pivot table, pivot field and pivot item names in your workbook.
This code will run automatically the the pivot table is changed (PivotTableUpdate event), and keep one pivot item collapsed.
_____________
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
Get the Sample File
To get the sample file with the macro to keep one pivot item collapsed, go to the Expand and Collapse page on my Contextures website.
In the download section, click the link to get the file with the Keep Collapsed macro. The Excel file is zipped, and in xlsm format, and contains one macro, on the sheet with the pivot table.
_________________________
Excel Macro Keeps One Pivot Item Collapsed
_________________________