In this Excel tutorial, we will explore the reasons behind larger Excel file sizes and how to reduce Excel file size to smaller sizes, to zip, for mail, with macro, with Pivot table, with pictures, without deleting data, and without opening the file itself.
Whenever working with Excel, the file size can cause problems in some cases. Larger file sizes can cause slower performance and trouble when sharing files. There can be multiple reasons behind this larger file size and knowing these reasons is crucial to solve the related issues. A larger file size takes up a lot of disk storage and sometimes can freeze your computer even take a long time to just open them. Whether you are dealing with complex formulas, large datasets, or formatting, you can save a lot more than just disk space by following this article.
⏷Find Out What Is Causing Large Excel File Size
⏷Reduce Excel File Size
⏵Minimize Picture Size for Sharing
⏵Remove Excess Formatting
⏵ZIP Excel File
⏵Clear Pivot Table Cache
⏵Compress Embedded Objects While Uploading
⏵Delete Unnecessary Data or Sheets
⏵Remove Unnecessary Conditional Formatting
⏵Keep Only Used Range in Worksheets
⏵Remove Needless Formulas
⏵Get Rid of Watches
⏵Delete Unnecessary Hidden Data
⏷Reduce Excel File Size Without Deleting Data
⏵Compress an Excel File to a Zip
⏵Save File in Binary Format
⏵Optimize Used Formulas
⏷Reduce Excel File Size Without Opening
⏷Available Tools to Reduce Excel File Size
⏷Frequently Asked Questions
⏷Reduce Excel File Size: Knowledge Hub
How to Find Out What Is Causing Large Excel File Size?
We can use data compression programs like 7-Zip, Gzip, WinZip, etc. to see which items in your Excel file are responsible for its large size. Here, we will use the 7-Zip data compression program as it is free and open-source. You can download the install file from its official page.
- Select and right-click on the file >> select 7-Zip >> select Open archive.
- You can see media and worksheets are causing the larger file size.
How to Reduce Excel File Size (11 Suitable Methods)
If your Excel file contains pictures, large embedded objects, PivotTables, Watch Windows, or hidden data then the file size can get large. Depending on the contents of your Excel file, you may need to apply one or multiple methods to reduce your Excel file size. Here are 11 suitable methods to reduce an Excel file size.
1. Minimize Picture Size for Sharing
The reason behind the large file size can be that it contains pictures. You can follow the following steps to solve this problem.
- Select the picture >> go to Picture Format tab >> select E-mail (96 ppi): minimize document size for sharing >> select OK.
2. Remove Excess Formatting
Another reason behind larger Excel file size is excess formatting. Go through the following procedure to clear this formatting and reduce file size.
- Select the cell range that contains unnecessary formatting >> go to the Home tab >> select the drop-down button for Clear >> select Clear Formats.
3. ZIP Excel Data File
If you have a file with large datasets then you can follow this procedure to reduce Excel file size.
- Change the File Name Extension to zip >> select Yes.
4. Clear Pivot Table Cache
Pivot table Cache can be another reason for the large file size in Excel. Here is a solution to that.
- Right-click on any cell of the Pivot Table >> select PivotTable Options.
- Disable the Save source data with file option >> enable the Refresh data when opening the file option>> click OK.
5. Compress Embedded Objects While Uploading
We can insert objects like PDF, MS Word, MS Excel, MS PowerPoint, Pictures, etc. files in Excel. These files are called Embedded Objects and Embedded Objects and these objects can cause a larger Excel file size.
For example, consider the following worksheet with an embedded MS Word File. We will compress this embedded object to reduce our Excel file size.
- Right-click on the embedded MS Word file >> select the Document Object option >> click the Open command.
- At this time, the MS Word file will open. As you can see, there are a lot of pictures in this file.
Click the GIF for a detailed view
- Select any image and you will get the Picture Format tab. Go to the Picture Format tab and click the Compress Pictures option.
Click the image to get a detailed view
- In the Compress Pictures dialog box, disable the Apply only to this picture option >> enable the Delete cropped areas of pictures option >> set to Resolution to E-mail (96 ppi): minimize document size for sharing >> click the OK button.
You can click this image to get a detailed view
- Press the F12 key to open the Save As dialog box. Go to the directory where you want to save the compressed file >> enter a File name >> set the Save as type option to Word Document >> click the Save button.
Click the image to get a detailed view
- Return to your Excel worksheet. Select the embedded object and press the Delete key to remove the previous object.
- Now, select the cell where you want to insert the object >> go to the Insert tab >> select the Object option from Text group.
Click the image to get a detailed view
- In the Object dialog box, go to the Create from File tab >> select Browse and select the compressed file from the directory where you saved it >> enable Link to file and Display as icon options >> click the OK button.
- Your compressed object will be uploaded in the Excel file.
6. Delete Unnecessary Data or Sheets
- Select unnecessary sheets by holding the Ctrl key >> right-click on the selected sheets >> select Delete.
7. Remove Unnecessary Conditional Formatting
- Select cells that contain unnecessary Conditional Formatting >> go to Home tab >> select Conditional Formatting >> select Clear Rules >> click on Clear Rules from Selected Cells.
8. Keep Only Used Range in Worksheets
- Select 1st unused row >> press Ctrl + Shift + Down Arrow (⬇) >> right-click on selected rows >> select Delete.
9. Remove Needless Formulas
- These marked cells contain the same formulas.
- Select the value and press Ctrl + C >> right-click on the cell where you want to paste the value >> select Values from Paste Options.
10. Get Rid of Watches
- Go to Formulas tab >> select Watch Window >> select all the watches >> click on Delete Watch.
11. Delete Unnecessary Hidden Data
- Select whole sheet >> right-click on rows or columns >> select Unhide >> delete unnecessary data from the hidden cells.
Note: Depending on the contents of your Excel file, you may need to apply one or multiple actions from the above-mentioned methods. For example, after minimizing picture sizes, removing excess formatting, clearing PivotTable cache, compressing embedded objects, and removing unnecessary data, formulas, and sheets, my Excel file size has reduced from 53 KB to 32 KB.
How to Reduce Excel File Size Without Deleting Data
If you don’t want to delete any data while reducing your Excel file size, then you can compress the file to a zip file or save it in binary format. You can also optimize the array formulas or nested functions used in your workbook.
1. Compress an Excel File to a Zip File
- Select the file >> right-click on it >> select Send to >> select Compressed (zipped) folder.
- Here, you can see that the file size is reduced.
2. Save File in Binary Format
- Go to File tab >> select Save As >> select Excel Binary Workbook (*.xlsb) as file type >> select Save.
After saving the file in binary format, my Excel file size has reduced from 53 KB to 49 KB.
3. Optimize Used Formulas
Complex formulas (e.g. nested IFs, array formulas, etc.) can increase the size of your Excel file. In such cases, you can use alternative simple formulas, and optimize the used formulas by avoiding unstable functions.
How to Reduce Excel File Size Without Opening
- Select Excel file >> right-click on it >>select Properties.
- Select Advanced from properties.
- Enable Compress contents to save disk space >> select OK.
After applying this method, my Excel file Size has reduced from 53 KB to 46 KB.
Reduce Excel file size using online tools (Free & Premium)
There are several popular tools online that you can use to compress your Excel files. Most of the tools handle most of the Excel file formats. Here is a list of tools that you can use to compress your Excel files:
- Aspose.app // This online tool compresses XLS, XLSX, XLSM, and XLSB Excel files.
- Fileformat.app // This online tool compresses XLS, XLT, XLTX, CSV, XLSX, XLSB, and XLSM Excel files.
- Reducefilesize.com // This online tool compresses .xls, .xlsx, and .xlsm files (max 50 Mb).
Frequently Asked Questions
1. Why Is Excel File Size Large?
There are many reasons behind larger file sizes. They are:
- Containing pictures.
- Large data sets.
- Embedded objects.
- Pivot table cache.
- Excess cell formatting.
2. How to Reduce Excel File Size?
Some ways of reducing Excel file sizes are.
- Zipping Excel file.
- Saving in binary format.
- Compressing image.
- Removing unnecessary formatting.
- Deleting Pivot table cache.
Download Practice Workbook
Conclusion
This article covers the reasons behind large Excel file sizes, how to determine these reasons, and how to reduce Excel file size in different cases using different methods. To conclude, you can say that this article covers everything about reducing Excel file size. We hope this article will be helpful for you. If you have any queries or suggestions, please let us know in the comment section below.
Reduce Excel File Size: Knowledge Hub
- Reasons Behind Large Excel File
- Determine What Is Causing Large Excel File Size
- Compress Excel File to Smaller Size
- Compress Excel File to Zip
- Reduce Excel File Size for Email
- Compress Excel File More than 100MB
- Reduce Excel File Size with Macro
- Decrease Excel File Size Without Deleting Data
- Reduce Excel File Size with Pivot Table
- Reduce Excel File Size Without Opening
- Compress Excel File Size with Pictures
- Zip an Excel File to Reduce Size
- Reduce Excel File Size by Deleting Blank Rows
- Reduce Excel File Size That Is Too Large for No Reason
Hi
I found that if you save your workbook as a binary file in the format .xlsb, it saves a great amount of space by reducing a large file by about 60%!!
That is really a good percentage of reduction. Thanks for the feedback, Abraham.
What I have noticed is that if I copy an excel file from a network to my computer the size increases exponentially. The way to reduce the size is to Break links to the original file,or copy paste values from network file into a new file on my computer.
I have two, very different, files each of 45,737KB. This seems to be a remarkable coincidence. I wonder if there might be a problem when they expand, as they will do with time.
Incidentally they take under 8 seconds to load – I have a 64 bit version of Office. The access time is .0035ms
Thank you Kawser .. that’s really helpfull
This list of ways to reduce excel file size is so comprehensive that i do not need to look any further. Thanks a lot for this.