Set default expand/colapse state on pivot tables
- by CLockeWork
The Setup
I have a pivot table in tabular form pulling data from an Analysis Services Cube.
I want to calculate the number of days between two dates, but the setup will only allow me to pull in all date elements, not just the date. I’ve been able to deal with this easily enough by just grouping all the columns:
The Problem
The default state for the expand/collapse buttons in the image above is often collapsed, but that means the dates I need aren’t there and you have to open the group and manually expand them.
This also happens in some random ways (as shown in the image) where only some rows expand.
The Question
I need a way to set these sections to always be expanded, so that the user never has to open the group to expand the rows. Ideally I’d like to avoid VBA because our end users often block it, but if that’s what’s needed then so be it.
Is there a way to set my pivot table to never collapse it’s predefined groups?
Note
the end user is using Excel 2010
