Only refreshing pivot table you are working on

[masplin]

I have a massive workbook with 50+ pivot tables. When I am building a new one and add a field I have to wait 2 minutes while it recalculates every single pivot table in the workbook. This makes building new pivot tables incredibly painful. I've tried turning workbook calculation to manual but doesn't make any difference. This seems the wrong behavior as completely unnecessary to recalculate the other pivots as data not changing. Is there someway round this please?

Thanks

Mike

 

[Jerry Sullivan]

Hi Mike,

You posted your question in the PowerPivot forum. I haven't used that, but since you haven't had any replies, I'll offer a suggestion that should help whether or not you're using the PowerPivot Add-In.

When a PivotTable is refreshed, any PivotTable that shares the same PivotCache is refreshed at the same time.
When you make new PivotTables by copying existing PivotTables, they will typically continue to share the same cache.

This has some benefits in file size and consistency, but also some downsides as you describe.

Try following one of the methods in this article to "unshare" the PivotCaches.
Unshare a data cache between PivotTable reports - Excel - Office.com

 

[masplin]

Hi Jerry. Very useful. I think the problem will be that the reason they all share the same cache is the workbook loads using about 1GB of memory. I guess if I have more caches the memory usage will increase substantially. I can understand the behaviour if you hit refresh, but seems odd when you are just adding fields. I tried using the "delay update" on the regular pivot table field list, but just doesn't seme to respond. Sounds like if i'm building a new report I can go through your procedure to delink it while building and then reconnect one finished.

Apppreciate the help

mike