Keeping Track of Pivot Tables in Complex Workbooks

The Source Data Problem

Every pivot table has a source data range. When you create multiple pivot tables from the same data, they should share a cache — this saves memory and ensures they refresh together.

But if you're not careful, Excel creates separate caches for each pivot. Now you have pivot tables that look like they're based on the same data but actually have separate copies.

To share a cache: when creating a new pivot table, the wizard asks “Do you want to analyze multiple tables?” and gives options to use existing pivot table data. Choose wisely.

Finding Your Pivot Tables

Excel has no built-in “list all pivot tables” feature. You can:

• Click into a pivot table and the PivotTable Analyze tab appears — but you have to find the pivot first
• Use Go To Special (Ctrl+G → Special → Objects) to highlight objects on the current sheet, then look for pivots
• Check each sheet manually — tedious but sometimes necessary

With 20+ sheets, manually checking each one is painful. And you might miss pivot tables hidden in scroll regions or on hidden sheets.

Refresh Strategies

Refresh All: Data → Refresh All updates every pivot table and data connection. This is usually what you want, but can be slow in large workbooks.

Refresh on Open: Right-click a pivot table → PivotTable Options → Data → check “Refresh data when opening the file.” Good for pivots that should always show current data.

VBA refresh: If you need control over refresh order or want to refresh specific pivots, VBA can iterate through pivot tables and refresh them programmatically.

Naming and Documentation

Like charts, pivot tables get default names: PivotTable1, PivotTable2, etc. You can rename them in PivotTable Options or via the Name Box.

Descriptive names like “Sales_ByRegion_Quarterly” help when you need to reference pivots in formulas (using GETPIVOTDATA) or VBA.

For complex workbooks, consider a documentation sheet that lists each pivot table, its source data, and its purpose. This takes five minutes to create and saves hours later.

Official Resources

• Create PivotTable — Microsoft pivot table guide
• Refresh PivotTable — updating pivot table data