This is a sneak preview of my upcoming Udemy course on Pivot Tables. This video explains the  relationship between Pivot Tables and File Size. Watch this 6 minutes video and learn from it.

Apply this knowledge to your existing pivots – especially those with large file sizes and see the difference. Make sure you try it out on a copy. Never learn using Live files.

Pivot Tables and File Size:
Additional Best Practices

Watch the video first.

Unnecessary data

It is quite common to import raw data as you get it and create pivots (or power pivots) based upon it. Pivot Tables and File Size also depends upon the amount of data you have. The assumption is that all the data you have imported is being used (or will be used sooner or later) in the Pivot tables.

Unfortunately, this assumption is not true in many cases. I have seen instances where 78 columns of data is imported and only 12 of these columns are being used in the Pivot.

No amount of Pivot Table Cache optimization is going to reduce the file size impact if you have redundant data in your files.

I strongly suggest you audit your large Excel files containing Pivots and remove unwanted columns.

Last Cell

Very often, the last cell is set to a very large number (row or column) increasing the file size unnecessarily. Go to each sheet and check last cell (CTRL END). If it is wrongly set, remove unwanted rows and columns, save the file and reopen it. It will reduce the file size.

Delete unnecessary pivot tables

Like presentations, report files grow over time. Often the reports are handed over from one person to another. In the process, some Pivot tables are just lying there. Nobody notices them, nobody is using them, but they are still occupying space. Most probably such ignored pivot tables are not using the same Pivot Cache – thus adversely impacting file size.

Pivot Tables and File Size should be managed proactively and carefully. It should not be left to chance.