Help to Reset Pivot Table

Use this forum for any general discussion and questions about Excel
MattAllington
Posts: 1159
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
hierarchy.png (10.18 KiB) Viewed 12580 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 Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

PhilC
Posts: 274
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.

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

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
   



Cheers
Phil

ella.uther
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.

RuskinF
Posts: 6
Joined: Thu Jan 23, 2020 8:30 pm

Re: Help to Reset Pivot Table

Postby RuskinF » Thu Jan 23, 2020 8:39 pm

I know this is a redundant step, but try it:
Click the PivotChart.
On the Analyze tab, in the Data group, click Clear, and then click Clear All. The Clear All command resets your PivotTable but does not delete it. The data connection, placement of the PivotTable, and PivotTable cache remain the same.

I hope this helps!

RadekZenek
Posts: 1
Joined: Tue Jul 07, 2020 6:16 pm

Re: Help to Reset Pivot Table

Postby RadekZenek » Tue Jul 07, 2020 6:20 pm

[quote="MattAllington"]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

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?[/quote]

You have in the settings marked "open all"

User avatar
Jordakson
Posts: 1
Joined: Mon Nov 02, 2020 1:21 pm
Location: Australia, Sydney

Re: Help to Reset Pivot Table

Postby Jordakson » Mon Nov 02, 2020 2:35 pm

I know this is a redundant step, but try it:
Click the PivotChart.
On the Analyze tab, in the Data group, click Clear, and then click Clear All. The Clear All command resets your PivotTable but does not delete it. The data connection, placement of the PivotTable, and PivotTable cache remain the same.

I hope this helps!


It really helped, I didn't expect it.
jokaroom-casino.com is the best choice for gamblers in Australia

Valentin01
Posts: 1
Joined: Wed Apr 21, 2021 7:43 am

Re: Help to Reset Pivot Table

Postby Valentin01 » Wed Apr 21, 2021 7:46 am

Did you check the "preserve formating" in the pivottable options?

Regarding the second questions you can add an additional button to your excel. Go to View|Toolbars|Customize the go to Tab Commands, choose Data and look for the "refresh data" button. You can drag this button to your toolbar.

User avatar
madina
Posts: 3
Joined: Sun May 30, 2021 1:49 am

Re: Help to Reset Pivot Table

Postby madina » Sun May 30, 2021 1:53 am

The problem I got is how to reset the expand and collapse buttons back to "All collapsed". It is easy to collapse the top level, but when I expand it again, all previously expanded levels will return to the expanded state. This is not what I want. I need another way to reset the pivot table so that all levels are collapsed back to a fully collapsed level so that the user can start reading the drill down again.

User avatar
madina
Posts: 3
Joined: Sun May 30, 2021 1:49 am

Safety uniforms made in Turkey.

Postby madina » Fri Jun 11, 2021 11:50 pm

In the category of protective clothing and uniforms, there are models of special protective clothing made especially for you. ATA Uniforma, which produces special protective clothing for men, women, summer and winter, produces special protective clothing for you.

User avatar
madina
Posts: 3
Joined: Sun May 30, 2021 1:49 am

Police uniforms in Turkey.

Postby madina » Mon Jun 14, 2021 5:05 pm

In the category of Police Uniforms and Uniforms, there are special police clothing models specially produced for you. ATA Uniforma, which produces special police clothing for men, women, summer and winter, produces special production Police Uniforms for you.


Return to “Excel”

Who is online

Users browsing this forum: No registered users and 1 guest

cron