How do I stop PivotTable fields from expanding when the data set changes

Question

I'm finding that collapsed fields in my PivotTable are expanding by themselves when new data is added to the original source, which I don't want.

I have some source data:

I then create a PivotTable with expandable fields:

I want these fields to stay collapsed all the time, so I right-click on "Cat" and hit "Collapse entire field". Bingo!

However... as soon as I add a new animal to my original data table and refresh the PT, the new field appears expanded. I can click to collapse it again, but I thought collapsing all was changing the "default" state of that field, when instead it seems to be just collapsing all the ones I can see at the moment.

I know how to do this with VBA, but I can't have VBA in this workbook. I'm building this workbook with the OpenXML SDK, so if there's some sort of fix I can make in the XML markup that will be easy too.

Answer 1

There are no dedicated buttons on the pivot table to expand/collapse the entire field. But it can be done at locations other than using the expand/collapse buttons on the individual rows. Generally for the following options, a cell inside the Rows area of the pivot table must be selected for these options to be enabled.

1. Use the right-click menu. Click Expand/Collapse, then Collapse entire field.

2. Use the Options/Analyze tab in the ribbon. Press the Collapse Field button.

3. Keyboard shortcut to Collapse Entire Field:
   Alt, A, H Or Menu Key, E, C

Excel 2016 adds buttons on pivot charts that expand or collapse fields in the pivot table.

See this Excel Campus Webinar, which was also the source of the images.

If this is generally the route you're using to collapse fields (and it is unsuccessful), it means you're running into a different issue. When you include a field in a pivot table, Excel generally wants to display it. If you change the data and update the pivot table, Excel will want to display fields built into the table that have not been filtered or collapsed. You may need to control it at the pivot table definition.

Display the PivotTable Fields window, and deselect/select the Name field there.