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. The full video transcript is at the bottom of this page
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
Here is the full transcript of the video shown at the top of this page: Video: Expand and Collapse Pivot Fields
When you first create a pivot table and add fields to the layout, everything is visible. In this pivot table, you can see that I've got four fields in the rows area and one value field.
In this cell, I'm going to use a formula just so we can keep an eye on the pivot table total. So I'll type an equal sign (=) and then scroll down to the very end, and click on the grand total.
Press enter: that puts a GETPIVOTDATA formula in here, and we're just getting the total quantity and I'm going to format it with a comma, so it's easier to read and get rid of those decimal places.
So there's our number, and we can see what's happening in the pivot table, just by keeping an eye on that total.
Instead of seeing everything, you might want to focus on particular regions or products, so you can apply filters.
In this one, I'm going to click on the category, so this is a category called Bars.
I'd like to only show a couple of categories, so in the dropdown I'm going to remove the Crackers and Snacks categories. Now we can see just two, and our grand total has gone down.
Another way to focus in a pivot table, is instead of filtering items out, just hide the detail. So with the bars, maybe I don't need to see the detail on all those different types of bars, but I'd like to keep the total there unchanged.
So one way to do that is to double-click on that item: that hides its details, but it didn't change the subtotal. All the detail is still in there, it's just hidden away for now.
You'll see that it also hid the detail for the New York bars and Philadelphia. So that item throughout the pivot table is now collapsed. We can't see its detail.
If I want to see it again, I can double-click and that brings it back throughout the pivot table. So that's collapsing and expanding a particular item.
Another way to do that is with little plus and minus buttons. Those aren't visible in my pivot table.
I can show them, if I select a cell in the pivot table, and on the ribbon under PIVOTTABLE TOOLS, I'll click ANALYSE, click the drop-down for Show, and here's the +/- Buttons.
I'll click that, and this toggles it on and off so if I clicked it again, it would hide them, click it again to show.
So with these buttons, we could be doing the same thing as double-clicking. So here it's a minus, so if I click that it collapses; if I click the plus sign, it expands.
We can also use commands on the right-click. So if I right-click on Boston, and point to Expand/Collapse, I can collapse the entire field; so that's going to collapse all the cities.
So all the cities now just show the city name and the subtotal, so we've collapsed it to this level.
If we want to expand, we can use that right-click command again. So I'll right-click on Boston, Expand/Collapse, and I'll expand it to Category.
So that only shows the Boston categories. It left all of these other cities collapsed.
If I want to see the same thing for all the cities, I can expand that whole field. So I'll right-click on Boston, Expand/Collapse, and Expand Entire Field. So now all the cities are expanded, and we can see that last level that we expanded to a minute ago.
And if I want to see this last level, I can right-click on one of the cities, Expand/Collapse, and now I'll expand the pivot table to product. So now we can see all the product detail under every category and city and region.
So if you're going through the pivot table, and hiding some things, and showing other things; eventually you might want to get it all back to its original condition, of showing everything fully expanded.
You can do that with two steps.
So that puts everything back, because we've shown the detail right down to the lowest level field, and then expanded the top level field
Return to Video: Video: Expand and Collapse Pivot Fields
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: October 7, 2021 11:38 AM