Pivot Table Refresh Best Practice-Reducing File Size vs Opening Time

[owentmoore]

I have a lot of pivot tables in my file. In order to reduce the file size I have made the source data of all the pivot tables the same as the first pivot table - correct so far I hear you say.

I now want to open the file with only the pivot tables on the first sheet refreshing which allows it to open much faster. My source data is a named range say "PivotName". Obviously when I refresh one pivot, all the pivots of the same source refresh.

My question is - to avoid the pivots on the other sheets refreshing should I change the source of the other pivot tables or is there a better way that will not increase my file size?
If I change the source then do I need to create a new named range with the same source data but different name, ie "PivotName1"

Thanks
Owen

 

[SkipVought]

Hi,

"Obviously when I refresh one pivot, all the pivots of the same source refresh."

No so!

You must REFRESH any and all PTs regardless if they share the same source.

Skip,

_[red][/red]

 

[owentmoore]

I must be thinking of the Refresh On Open option. When this is selected all the pivots with the same source refresh?

 

[SkipVought]

Refresh On Open? Whuchever you have selected for that option.

Skip,

_[red][/red]