How to GETPIVOTDATA from collapsed fields

Question

I have a Pivot table that summarizes financial data from a transaction history spanning many years.

I use GETPIVOTDATA to extract some of that data for further analysis.

Is there a way to force Excel to access the data even if the respective pivot table field is collapsed?

The great thing about the Pivot table is that I am able to collapse columns or rows as needed for visualization. In my case, Pivot table columns are organized by years and months. I have data going back all the way to 2018 and I don't need to see that data broken down by month so I want to keep the years 2018 to 2022 collapsed. That way it's easy to have yearly averages next to one another. But then for data referenced with GETPIVOTDATA, any cells referencing fields that are collapsed get the REF error. Is there any way to prevent that from happening? I thought GETPIVOTDATA was there precisly to extract data from the table dynamically – irrespective of the actual cell where the data shows up, in case that the table is refreshed with new categories. But that doesn't seem to work that way. Any help how this might work greatly appreciated!

Answer 1

Hi jan_dh,
Thanks for your post in Microsoft Community.

The behavior you're observing is indeed normal. According to the description of the GETPIVOTDATA function, its operation is based on referencing cells. Once the cell being searched for is no longer present in the table because its corresponding field is collapsed, it will directly return a reference error.

For more information, please see: GETPIVOTDATA function - Microsoft Support

Should you indeed have this requirement, you're welcome to submit your feedback through the Feedback Hub. Hopefully, the Microsoft development team will enhance this feature in the future to accommodate such needs.

I hope the information above is helpful to you!

Best Regards, 
Thomas C - MSFT | Microsoft Community Support Specialist