Excel like a finance pro.
Pivot tables · Data & refresh
The pivot cache
A copy of the source data that the pivot reads from, shared by pivots built from the same source.
When to use it
The pivot cache is the copy of the source data a pivot reads from. Pivots copied from one another share a cache, which keeps files smaller and lets slicers span them.
The shape of it
- How
Pivots created by copying share a cache; changing the source on one changes all. Save without the cache under Options to shrink files.
Worked examples
Shared cache
Copy a pivot and paste it elsewhere → Two pivots on one cache; refresh either and both update
Shared cache.
Separate cache
Create a second pivot from the same range with Insert, PivotTable → A separate cache with the same data
Independent grouping possible.
Drop the saved cache
PivotTable Analyze, Options, Data, uncheck Save source data with file → A smaller file that must refresh on open
Shrink files.
Worth knowing
- Shared caches share grouping and calculated fields.
- Separate caches let two pivots group the same field differently.
- Excel asks whether to share the cache when you build a second pivot on the same source in older versions.
Where it goes wrong
- Dropping the saved cache means the source must be available when the file opens.
- Many separate caches on the same data bloat the file.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.