Someone in the past asked me to help reduce the size of her excel file. It’s over 100mb and killing her storage space.
I did a few adjustments and it went down to 1mb. 100-fold decrease in size.
1. Remove Unused Spaces
Go to your Excel file, press Ctrl + End. How far did it reach? Do you need all these cell space?
Sometimes, you might selected a cell at the far end of the excel sheet. You realized the mistake and return to the original location. Easy right? No issue here?
Wrong. You selected a cell at the other end, and effectively turned the space between this cell and A1 as active sheet. Even if you didn’t do anything in these cells, the fact that they are active takes up memory size.
What you can do to remove this dead space is highlight the column after the last cell that you wanted to use, and click Ctrl + Shift + Right to reach the end of the active column. Right click and delete all these columns.
Do the same for the the rows. Highlight the row after the last cell that you wanted to use and click Ctrl + Shift + Down to reach the end of active row. Right click and delete all these rows.
Do the same for every sheet in the file and you removed the dead space inside.
2. Too Many Pivot Tables
No, I can’t remove my pivot tables. They are essential to my reporting. Sure, but that depends on how it’s created.
When you create a pivot table from raw data, it also creates a copy of the raw data in memory. So, each time you create a new pivot table from the raw data, it creates another copy of the raw data. Even if the raw data in question are both the same. See where I’m going with this?
With each pivot table you are creating, you making multiple copies of the raw data. However, if you copied the first pivot table and paste it, the new pivot table will refer to the source file of the first pivot table. You are not creating another copy of the raw data this way.
So, if you have multiple pivot tables that referred to the same raw data. Delete the rest and start over copy and pasting the first pivot table. You are preventing the raw data from being created over and over again.
3. Changing Excel format
The smallest excel format I used is a xlsb ending. The default excel format nowadays is xlsx and that compressed the size a lot over the previous xls format. So, if you are still using an xls format, or inherited a file from years ago, consider saving it in xlsx or xlsb for even more compression.