Help to Reset Pivot Table

Use this forum for any general discussion and questions about Excel
Posts: 849
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Help to Reset Pivot Table

Postby MattAllington » Fri Feb 06, 2015 7:25 am

I have a pivot table connected to a power pivot data model. I have products on rows and the products are set up in a hierarchy in the data model. The net result is that I have expand/collapse buttons on rows (see image)
hierarchy.png (10.18 KiB) Viewed 2110 times

The problem I have got is how to reset the expand collapse buttons back to "All Collapsed". It is easy to get the top level to collapse, but when I expand it again, all the previous expanded levels return to the expanded state. This is not what I want. I want a way to reset the pivot table so that all levels are collapsed back to the fully collapsed level so the user can start drilling into the data again. I assume there is a way to reset this but I can't find it in Excel and I can't find it on the web.

Any ideas?
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at

Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Re: Help to Reset Pivot Table

Postby PhilC » Tue Feb 17, 2015 9:29 am

Hi Matt,

The Rows have what is called the Active Field. Whichever item in the Rows you have selected will be the Active field. Collapse Field works on the active field. If you collapse each field from the bottom one up in turn, I believe you will get the behaviour you require. That is the manual way.

I suspect you would be able to have a macro that will collapse each one in turn up to the parent level. The code looks something like this - which looks like you can add a command for the different levels in your hierarchy. Perhaps someone will have a more elegant way of looping through the levels.

Code: Select all

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[SMTLines].[SMT Layout].[SMTLevel1]").DrilledDown = False
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[SMTLines].[SMT Layout].[SMTLevel2]").DrilledDown = False
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[SMTLines].[SMT Layout].[SMTLevel3]").DrilledDown = False


Posts: 1
Joined: Wed Sep 28, 2016 7:46 pm

Re: Help to Reset Pivot Table

Postby ella.uther » Wed Sep 28, 2016 7:47 pm

newbie here.

Return to “Excel”

Who is online

Users browsing this forum: No registered users and 1 guest