Excel takes a snapshot of your data set when you create a Pivot Table report and stores it in a pivot cache.  This is just an area where your data source is duplicated for easy access, this is important to understand as each time you create a a Pivot Table report from a separate data source your file size increases also.

This can bloat your file size and this increase in file size will depend on the size of your original data.  It can easily double the size of your workbook.

I have a few tips to avoid Pivot Table Bloat.

1. Delete Your Source Data Tab.  

If your Pivot Table and your source data tab your are definitely wasting space.   You are keeping two copies of the same data!.  You can safely delete your source data and your Pivot Table will function just as well.  Once saved your source fill size will reduce.

This type  of action is useful if you are sending your Pivot Table to others to use, the only functionality lost of the ability to refresh as the original data is not there, but as a once off piece of analysis- I find it’s the right way to reduce the size of the Excel file.

2. Copy And Paste -Don’t Create From Scratch

As I said in the introduction to this tip, when we create a Pivot Table, a pivot cache is created by Excel, so it makes sense if you are making multiple Pivot Tables from the same same data source, you use the same pivot cache right?.  In this way we again prevent the Excel file size bloating.

When you want to create a new pivot Table form the same data  just copy an past the original table, then edit and changes as required.  We have just avoided creating another cached area of the same data.  Well done guys!

Advanced Excel & Power Pivot Training Classes

Become a Data Analysis & Dashboarding Monster!