Clear Pivot Table Cache

feature suggestions
1

Have you considered adding a feature to clear the Pivot Table Cache? I have run into issues when my raw data is updated the filters/slicers still show old (and new ) data. I did not understand this till I realized it was a Pivot Table cache. There is a manual clumsy process to clear the cache. However I though this might be a good addition to your tools.

thanks and keep up to good work!

Alan

2

Hi Alan,

I’m not familiar with Pivot Tables. Could you describe briefly the manual process to clear the cache?

It will certainly not be feasible to fully automatically clear the cache (because it would forfeit the very benefit of a cache, all the time), but if there is an internal command that clears the cache it might be quite easy to add a button to the XL Toolbox ribbon.

Best

Daniel

3

I would not want to clear cache all the time just a button. I have seen a few other tools to it. The problem showed up in my case when I some data (only 300 lines or so). I had a Pivot table on it along with some slicers. Then when I got and updated data set (same columns) more rows and some items on existing rows changed. I pasted over the old set with the new set. Did a refresh all. My filters showed old data values that had since been updated. i.e. Last Login date was updated but the cache on the filters showed old values that were no longer there.

These links give examples and even some related VB code to correct. I know i could do it other ways but would love to see it added to your tool.

https://www.contextures.com/xlPivot04.html

https://www.extendoffice.com/documents/excel/4238-excel-clear-filter-cache.html

thanks

Alan

4

@Alan_Jones Please try out version 7.3. It has a new ClearPivotCache command. It’s a bit hidden though as I did not add a new ribbon button for it. The default keyboard shortcut for it is CTRLSHIFTF5. You must reload the default keyboard shortcuts though, or you can define your own preferred shortcut.

Hope this works for you. I basically just did what’s suggested on the internets. Since I don’t use pivot tables myself (only very rarely), I did not really have the use case to try this out.