Excel

Why is my Excel file so large? (Pivot tables are usually why)

4 minute read

You've deleted the extra sheets, trimmed the used range, and the file is still 40MB for a workbook with one visible pivot table and a modest data set. If there's a pivot table anywhere in the file, there's a good chance you've found your answer — and it's not the pivot table itself, it's what's sitting behind it.

The hidden duplicate: the pivot cache

A pivot table doesn't read live from your source range every time you interact with it — it reads from a pivot cache, a complete internal copy of the source data that Excel stores inside the workbook specifically so the pivot table stays fast and responsive. This is normally invisible; there's no sheet or cell you can point to and see it.

The trouble is scale. If your source data is 50,000 rows, the pivot cache is a second copy of roughly all 50,000 rows, compressed into the file alongside everything else. Multiple pivot tables built from the same or overlapping source data can each get their own cache, multiplying the effect. A workbook can easily be 80% pivot cache and 20% everything you can actually see.

Why you can't just delete it

The cache isn't optional clutter — it's what the pivot table actually reads from when you filter, sort, or rearrange it. Delete it outright and the pivot table breaks. What you actually want is for Excel to rebuild it fresh from source rather than carry around a static copy indefinitely.

Shrinking it the manual way

1. Click into the pivot table.
2. Go to PivotTable Analyze → PivotTable → Options.
3. Under the Data tab, untick Save source data with file, and consider unticking Refresh data when opening the file if you don't need it live.
4. Save the file — this doesn't shrink the existing cache immediately in all Excel versions, so a subsequent File → Info → Compress Workbook (where available) or a full refresh-and-resave often helps clear it out.

This works, but the exact behavior varies by Excel version, and it's easy to end up with a pivot table that no longer refreshes automatically because a related option got switched off at the same time.

The automatic way

The cache can be cleared directly and precisely — emptied out at the file level while the pivot table definition itself, and its connection to the source range, stay completely intact. Set correctly, it also flags the pivot table to refresh itself the next time the file is opened, so nothing looks broken or blank when you or a colleague opens it next.