Problems with expanding or colapsing entries in pivot tables

Copper Contributor


I would like to get the following annoying behavior of Excel under control:
Every time I expand or collapse an entry in a pivot table, I lose total orientation.
This is because everything in our very large pivot table is grouped by year and month. So if you expand the subentries "Customer A" under "December", all other months of "Customer A" are also expanded. This can, of course, extend the table enormously in one go. This would not be a problem if Excel kept the focus on the row that has just been expanded. So you often find yourself no longer in December but in February, for example. Having to scroll back to the place you have just expanded can be very frustrating.
Is there perhaps a setting that
- prevents all columns with the same name from expanding as well?
- keeps the row in focus whose subentries have just been expanded.
I would be very grateful for help.
Thank you very much.

2 Replies

@JoMueHo Unfortunately from what I've read there doesn't appear to be a setting to stop this behavior. When the same pivot items appear in more than one group of the parent field, they will expand and collapse together (when visible). You can, however, start by collapsing the entire parent field ("Years" in this case), then expand one year, month and customer at a time. In the example shown below, I've used years, quarters and months as a quick demonstration:


Collapse entire parent fieldCollapse entire parent field


Expand one parent itemExpand one parent item


If you expand two or more years at one time, quarters and months will expand/collapse together for those years. Sorry for the bad news... I realize this is probably not what you were hoping to hear.


For additional info, please see: 



thank you.

Even if it is a pity that there is no solution here.