Collapse Field in Pivot Table doesn't always work...

Question

Having trouble using the "Collapse Field" button in a pivot table.  

I swear I didn't have this problem before my company just recently switched over to Office 365 (we had Excel 2010, and now we have Excel 2016)

I will include a picture... it used to be that when I had one or two fields expanded,  if I clicked on a field that I wanted to be completely collapsed and then on the "collapse field" shortcut button, the entire pivot table would collapse down to that level.  So if I wanted the pivot table to be completely collapsed, I could just click on my highest level (year in this case) and the collapse field button and the pivot table would collapse to its most compact form.  Now, it seems some things will collapse this way, but others will not.  For instance, in the picture below, if I click on the Quarter and collapse field button, it will collapse down to Year + Quarter.  But if I click on the year with the collapse field button, it does nothing.  Interestingly, if I click on the year and the EXPAND field button (so now everything is expanded out as far as it will go) and THEN click the year and collapse field, it will collapse all. But that seems like going around your ear to get to your elbow (or however that saying goes...)

Answer 1

Hello Zixuan,

I seemed to find some success by simply dragging the date fields out of my field list and adding them back in... so technically, nothing changed, but somehow it fixed the problem... The Quarters are now nested under the years like they should be and the collapse field works on the year.  I don't understand what was wrong because when I dragged the items out and added them back in, I didn't change anything as far as the order, but for whatever reason, it seemed to work.  This is how my experience goes with Excel... things don't work and then they do and I never get to know what was wrong or why things changed... but since its working now, I don't think I will bother with the safe mode unless I run into the problem again.  Thank you for your suggestions.

Answer 2

Hello,

from your screenshot it looks as if the years are nested underneath the quarters. That looks weird. Can you confirm the order of the fields in the Rows area of the pivot fields panel? It should be Years, Quarters, Months

Answer 3

Hi KimMT,

According to your description, my understanding is that in your instance, if you click on the Quarter and Collapse Field , it will collapse down to Year + Quarter. It works as expected.

But if you click on the Year and Collapse Field, it collapses down to Year like your picture, right?

If so, it works as well. As you said we could click on the highest level to completely collapse the pivot table, it will collapse all of other levels of detail and only show the highest level.

For clicking on the Year and Expand Field，it will expand the entire field by default.

If you want to choose which level of detail to expand, please click on the level and right click, choose Expand/Collapse, and choose the level.

If that's not your scenario, please capture a video to help understand the differences about Collapse Filed between Excel 2010 and Excel 2016. You can share it via Private Message.

Best Regards,

Zixuan

Answer 4

Hi, 

Yeah, I noticed it looks that way and I'm not totally sure why, but I definitely have my years at the top of the hierarchy.  

Answer 5

Hi Zixuan,

*You said: "But if you click on the Year and Collapse Field, it collapses down to Year like your picture, right?"*If so, it works as well. As you said we could click on the highest level to completely collapse the pivot table, it will collapse all of other levels of detail and only show the highest level.

The answer is no, it does not collapse at all when I click on year and collapse field UNLESS I first expand everything and then hit collapse field and THEN it will collapse down to its most compact form.  As far as using the menus to expand/collapse, yes I could do this.  I could also just use the

• and - buttons next to each year and collapse them this way, but my goal here is to be able to use the Collapse Field button to collapse all open fields. (like I used to be able to do with Excel 2010).  

I will try to take a video and upload it to you.  I don't have the capacity here at work to record audio, so it will be a silent film :-)  

Thank you.