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
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.
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.
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.
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.
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.
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.
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.
- Select any image and you will get the Picture Format tab. Go to the Picture Format tab and click the Compress Pictures option.
- 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.
- 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.
- 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.
- 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.
- Select unnecessary sheets by holding the Ctrl key >> right-click on the selected sheets >> select Delete.
- Select cells that contain unnecessary Conditional Formatting >> go to Home tab >> select Conditional Formatting >> select Clear Rules >> click on Clear Rules from Selected Cells.
- Select 1st unused row >> press Ctrl + Shift + Down Arrow (⬇) >> right-click on selected rows >> select Delete.
- 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.
- 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.
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.
- Select the file >> right-click on it >> select Send to >> select Compressed (zipped) folder.
- Here, you can see that the file size is reduced.
- 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.
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.
- 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.
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).
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
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.
- 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