Page 1 of 1

Help to Reset Pivot Table

Posted: Fri Feb 06, 2015 7:25 am
by 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
hierarchy.png (10.18 KiB) Viewed 6878 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?

Re: Help to Reset Pivot Table

Posted: Tue Feb 17, 2015 9:29 am
by PhilC

Re: Help to Reset Pivot Table

Posted: Wed Sep 28, 2016 7:47 pm
by ella.uther
newbie here.

Re: Help to Reset Pivot Table

Posted: Thu Jan 23, 2020 8:39 pm
by RuskinF
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!

Re: Help to Reset Pivot Table

Posted: Tue Jul 07, 2020 6:20 pm
by RadekZenek
[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"