Help to Reset Pivot Table

Use this forum for any general discussion and questions about Excel
MattAllington
Posts: 1103
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 6889 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


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"


Return to “Excel”

Who is online

Users browsing this forum: No registered users and 1 guest

cron