Did you ever wonder why your Excel File is so huge while it actually has very little information?
When you receive an excel file it was in KBs but when you make changes and save, file size surprisingly increased to MBs!!
Fortunately there are several ways to overcome this problem, this article will help you learn the Top Ten Awesome Tips & Tricks to Reduce Excel File Size to a great extent without losing the actual content.
Tip 1: Zip the Excel file
If you Don’t want to edit or make changes to your Excel File, but still want to reduce the file size? Then the best option will be Zip’ing the excel file, it will reduce upto 90% of file size depending upon the images and objects in the excel file
Tip 2: Save the Excel file in .xlsb File Format
Do not want to edit anything in the spreadsheet (very similar to above case) only change is the file format in which you save the spreadsheet.
xlsb is Binary File Format, which will save your excel file in binary mode. Compressing/Reducing your Excel File Size to a great extent and the added advantage is, it can accommodate Macros unlike xlsx file format, and works way faster than any other excel file format.
Tip 3. Remove Unused Cells
Most people ignore this and burn their hand with huge file size and fatty lags.
What are these unused cells, basically there is a difference between ‘Actual Used Range’ and ‘Default Used Range’.
For example in the below given images, Actual data starts from cell ‘A1’ and lasts at cell ‘I14’ that is all we are interested in
But when I press CTRL+END key excel shows cell ‘I65526’ as the last used cell in my sheet. Surprising!! Yes it happens very often. The more beyond ‘Default Last Cell’ would be from ‘Actual Last Cell’, the more unnecessary size of excel workbook would it be having.
Do not worry just select the unused cells and delete the entire rows and save your file and see the change in file size.
Tip 4. Remove Cell Formatting
We do lot of formatting to the spreadsheet and we leave it there even though they are not adding any value to the main data. Removing formatting will save you more on file size, increase the spreadsheet performance and makes it look better or simple.
Follow below steps to remove formatting
- Select the range which you do not need any kind of formatting.
- Home Tab > Editing Group > Clear > Clear Formats
Best option if the spreadsheet contains lots of text.
Tip 5. Save the File in Right Version
Save your Excel file in the version that you are using. Saving your excel file in lower version will just make your file size increase. For example; if you are using Excel 2007 and above version, try to save your files in .xlsx, xlsm, .xlsb file formats unless otherwise the end user has the lower version.
Tip 6. Reduce the Size of Graphics
I Love Graphics in Spreadsheets, but not at the cost of pleasant spreadsheet performance, usability and presentability.
Remove Unnecessary Graphics as they take huge space.
Tip 7. Compress picture
Since picture take space and increases the file size, make sure you compress each picture in your spreadsheet.
Tip 8. Excessive use of Entire Column/Row Range in Formulas especially in Array formulas. I know how important and useful Array Formulas are, but try to use Array formulas as less as possible and lookout for alternative methods. As Array formulas will occupy for space and will slowdown spreadsheet performance.
Tip 9. Applying Conditional Formatting to ranges beyond the minimum needed to serve your purpose.
Tip 10. Employing more Query Tables: We do use query tables to get the data from external sources, but if you are done with the query table, better disconnect the table or just take the data and delete the query table as whole.
Here are the best possible ways to reduce the excel file size. What’s your way of choice in handling the excel file size. Leave your suggestions and questions in comments section below.
Note: Always make a backup of your original file before you try to compress it. While you’re trying out these tips, you could lose formatting, charts and other crucial components from your original workbook.
Also Read
I use Datarails.io, made my files smaller in approx 90%