Auto Refresh Data for Pivot Tables
I was there was an option to auto refresh pivot tables when data is changed in the referencing data table. I am trying to move massive tabular spreadsheets which rely on cross linking formulas across several worksheets to data tables which leverage pivot tables to produce the data. The data in the data tables can change very frequently and my team need to see the totals (pivot tables) in real time. The only way to do this right now is with VBA macros which isnt reliable it seems.
Actually, if a PivotTable and its source table are in the same file, we shouldn't need the PivotCache. The source table itself could act at the cache, giving us the same speed as a PivotCache without the increase in file size. And without a PivotCache, PivotTables in the same file as their source data would have no choice but to automatically refresh.
At the very least, the storage of PivotCaches within the file should be optimized so that the ZIP compression of XLSX, XLSM, and XLSB files notices the strong similarity between the PivotCaches and their source data and can compress the file much more.
Yeah, I noticed the need for a double refresh too. I had some PivotTables based on query tables. I'd get rid of something from the query table's source, only to still have it show up as #N/A in the PivotTable on the first refresh. On the second refresh, what I deleted is indeed gone.
Roger Govier commented
I have been asking for this to be a switchable option for a long time.
Equally, at the moment, the Pivot Cache is refreshed first, and THEN the refresh of the underlying data source.
This is incorrect.
The Data Source should be refreshed first, and then the Pivot Cache to give the correct result.
Currently you need to do a double Refresh to be sure of having the correct answer.
That option would be nice. After all, we already have the option for manual and automatic calculation. There might be an issue with refreshing PivotTables whose source data is external, but we should at least have the option for PivotTables whose source data is in the same file.