Excel workbooks can hold data including formulas, data, images, graphs, and charts which ultimately makes them slow and heavy. When your workbook contains more than enough data, you most likely don’t realize that extra flab is also there. For this, you need to cut off unwanted data but it is a time-consuming process. So, what would you do when you need to reduce Excel file size?
Keeping Excel file size isn’t an easy thing that’s why people often tend to find ways to reduce file size. Whenever you have to send the Excel file email platforms most of the time don’t support oversized files. More than 50 MB of files are not easy to share with others.
No matter why you need to reduce the size of the file, Excel lets you avail different options to reduce size. These options include saving the file in binary format, deleting unwanted data, changing the resolution or compression of the embedded images, and much more.
Let’s dive in to see how many methods are there in this post. Before that, we will discuss some potential reasons that make your Excel file oversized.
Potential Reasons for Oversized Excel Files
Users often ask why Excel workbooks become oversized when the sheets contain data only.
Huge Data
If your sheet has more than 100,000 rows with many columns will need more space for storage because of overloaded data.
Pivot Tables
Do you think Pivot Tables can increase the size of an Excel file?
Well, yes the Pivot tables can also affect the size because each pivot table holds its own dataset that’s why the file size increases.
Add-ons and Plugins
You might have noticed that some plug-ins are making disturbances to the performance of the Excel workbook. You must stop using plug-ins when you see a drop-down in the performance of an Excel workbook.
Formatting
When the worksheets are formatted, you will face issues like increased file size.
Hyperlinks
If the file contains excessive hyperlinks, it can cause the crashing of the Excel files. Therefore, make sure you have not added more than enough external links to save the file from being crashed.
Media Files
Media files can also make your file oversized because of heavy images, logos, tables, graphs, or charts. Even a small file could become a big one when you add media files to it.
Compatibility Issues
Last but not least, sometimes your Excel version does not meet the specifications of your file. There could be some compatibility issues if the file does not match the Excel version you are using.
The Best Solutions to Reducing Excel File Size
Below you will find out the best options that can help you in reducing size.
Saving File in Binary Format
If you convert the regular .xlsx format to .xlsb, you can easily reduce the file size. The turning format is known as binary format. For this, you can follow the steps given below:
- Open the Excel .xlsx format file.
- Go to the File tab and choose the “Save As” option in the File Tab drop-down list.
- Click on the Browse option and you will see a dialog box.
- In the File Name field, add the file name and now click on the Save as type option. Choose the .xlsb format and press the Save button.
This is how reducing Excel file size become easier.
Change Unwanted Formulas to Values
In our next trick, we will discuss how you can reduce the file size by changing unwanted formulas to values. You can easily convert unwanted formulas into values by following the steps given below:
- Go to the Excel file and choose the entire worksheet, or else choose the number of columns that needed to be changed from formulas to values.
- Press the F5 key and a dialog box will appear. Click on the “Special” option.
- Another dialog box will appear and you need to click on the Radio button. Choose the Formulas option and click OK.
- You will see all the cells with formulas are selected. Now, click on the Home tab and choose the Paste option.
- Click on the Values option and then press the Paste button.
- Now, click on “Paste Values.”
- Or else you can even use a shortcut command, which is CTRL + ALT + V to open the dialog box. Choose the radio button for Values and click on the OK button.
Ultimately, you will notice the selected formulas are converted into values that reduce the file size.
Remove Formatting
Formatting adds beauty to your Excel spreadsheet by adding borders, highlights, and conditional formatting. However, at the same time, it increases the file size which can make trouble for you.
Well, clearing format is not a tricky thing if you follow the steps given below:
- Open the Home tab and choose the Editing menu.
- Click on the Clear Formats button from the Editing menu and it will clear the formats of selected cells.
- You can clear conditional formatting by using the Clear Rules option given in the Style section.
Compress Images in Excel
Adding images to the Excel files could be enticing however the best thing is to avoid putting unlimited images in the sheet. Or if you really need to add images, make sure to compress them. Here’s how you can compress the images:
- Choose the image you need to compress.
- Open the Picture Format tab.
- Click on the Compress button.
- Ensure that you uncheck the first box Apply only on this picture to compress all images in the Excel file. You must choose the resolution as well and the mail resolution option is highly recommended.
Summary
Reducing Excel file size is not something that you need to worry about. If you know the reason behind the increased size of the file, you can easily manage to reduce the file size. Or else you first have to find the cause of the oversized file before applying the solution.
