Microsoft’s Excel is one of the best spreadsheet managing software that the world has ever seen. It’s packed with tools to help users crunch as many numbers as possible.
However, jamming all that data in an Excel file can result in huge file sizes. Since Excel is not a web-based tool, many people collaborate on these files over email, which means that file size becomes important.
In this article, we’re going over seven ways you can reduce Excel file size.
Also read: How to remove duplicates in Excel?
Remove unnecessary worksheets or data
One of the most direct factors that affect the size of an Excel document is the amount of data and the number of worksheets it contains. By removing excess or unnecessary data and/or worksheets, you can have a pretty significant impact on the size of your document.
Compress the file
A rather simple way of reducing file size is to compress or zip your file. This will get you approximately a 10=15% reduction in file size and doesn’t require any special software.
All you need to do is right-click your Excel file, hover your mouse on the Send to option and click on the Compressed (zipped) folder. Windows will zip your file, and then you can share it with anyone.
Also read: How to clear Recent List in Word, Excel and PowerPoint?
Convert the document to binary
Excel files are saved with a .XLS extension by default or simply put as Excel files. However, you can convert your document to binary or .XLSB to save on file size.
The way this works is the fundamental difference between how the two formats save data. .XLS saves data in the XML format, but.XLSB saves data in the .BIN format, which results in smaller file size. Being smaller in size, these files also open and load faster.
Remove unnecessary formulas
Just like data, having too many formulas can cause your file sizes to up significantly. Here’s what you can do to convert formulas to values in Excel quickly.
Step 1: Select your entire worksheet or data and press F5.
Step 2: Click on Special in the Go To window.
Step 3: Select Formulas and click OK.
Step 4: This will select all cells with formulas in them. Now copy them by using Ctrl + C.
Step 5: Head over to the Home tab and hover on the arrow below the Paste button and click on the Paste Values button.
Note that volatile formulas such as OFFSET can have an even bigger impact on your file size. You should try to simplify them down to non-volatile formulas or convert them to values.
Also read: How to Spell Check in Excel?
Compress your images
If you’re dealing with a spreadsheet that has images in it, your file sizes will be impacted rather significantly. The best way to avoid this is by compressing your images before adding them to your Excel documents.
However, there’s an inbuilt tool in Excel to compress images as well. Here’s how you can use it to save on file size.
Step 1: Select the image you want to compress and head over to the Format tab.
Step 2: Click on the Compress Picture button under the Adjust group.
Step 3: Select Email under Resolution and click OK,
Avoid saving the pilot cache with the file
If your spreadsheet includes a pivot table, you can reduce your file size by not saving the pilot cache with the file itself.
Step 1: Select any cell in the pivot table.
Step 2: On the Analyse tab click on Options under the PivotTable group.
Step 3: Head over to the Data tab and uncheck Save source data with file. Once that’s done, check the Refresh data while opening the file checkbox.
Also read: How to convert Seconds to Minutes in Excel?
Remove any formatting
This will not significantly impact your document’s file size, but if you’re looking to shave off a few MBs, this can help.
Step 1: Select your entire dataset.
Step 2: Head over to the Home tab and click Clear under Editing.
Step 3: Click Clear Formats to remove all formatting.
Remove conditional formatting
Conditional formatting also takes up space similar to regular formatting. However, removing it will also help your document load faster. Any time you change your worksheet, conditional formatting recalculates everything, leading to a slower spreadsheet.
Also read: How to add prefix and suffix in Excel?
