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 !!
Here is how to reduce size of an excel file.
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 .
If you don’t know, how to zip your excel sheet or getting any problem, while trying to zip it. Create a FREE support ticket HERE. We can help.
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.
OUR MOST TRENDING BLOGS:
- HOW TO COMPLETELY DELETE YOUR GOOGLE HISTORY
- 9 SIMPLE WAYS TO GET RID OF SLOW GOOGLE CHROME
- 98 GOOGLE TRICKS YOU WISH YOU KNEW BEFORE
- 13 BEST VLC TRICKS AND HACKS YOU NEED TO KNOW RIGHT NOW
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.
Other Excel Related Articles:
- Complete List Of Microsoft Excel Shortcut Keys
- How to use LOOKUP Function in Excel
- How To Convert PDF Files To Docs Files Using Google Drive
- What Is A Pivot Table And How To Use Pivot Tables In Excel?
- How to Calculate Compound Interest In Excel?
- How To Convert Excel To Word?
- How to Convert LBS to KG in Excel
- How To Calculate Standard Deviation In Excel
- How to Convert CM To Inches And Inches To CM In Excel
- How To Subtract In Excel With Excel Subtraction Formula.
- How to Convert Excel To PDF
- Basic Excel Formulas And Functions
- How To Use BMI calculator In Excel
- How To Multiply Cells In Excel
- How To Change Date Format In Excel
Thanks for the information
It was very usefull
How to reduce the excel file size when the pictures or photos are inserted
We are happy that you found it useful.
Hi, Thank you for sharing this information. But how can I reduce the size of the file which has many arrays, sum If and lookup formula’s without hardcoding the numbers??
How can I delete the unused columns after the last used column without having to hold the ctl key and right arrow through each column until I get to the last column in the sheet? This will take forever.
I have kept track of month to month expenses using a strip along the bottom of the spreadsheet that has now disappeared. How fo I restrore this strip?