Larger file sizes can cause slower performance and trouble when sharing files. There can be multiple reasons behind this larger file size, and understanding these reasons is crucial to solving the related issues. In this tutorial, we will explore the reasons behind larger Excel file sizes and how to reduce them.
⏷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
⏵Only Keep 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 identify which items in our 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. Download the install file from its official page.
STEPS:
- Install 7-Zip.
- 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
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.
Method 1 – Minimize Picture Size for Sharing
Reducing picture size can reduce file size significantly.
STEPS:
- In your Excel file, select the picture.
- Go to Picture Format tab.
- Select E-mail (96 ppi): minimize document size for sharing.
- Click OK.
Method 2 – Remove Excess Formatting
STEPS:
- Select the cell range that contains unnecessary formatting.
- go to the Home tab and select the drop-down button under Clear.
- Select Clear Formats.
Method 3 – ZIP Excel Data File
This method is suitable if you have a file with large datasets.
STEPS:
- In your File Explorer, change the File Name Extension of your Excel file to zip.
- Select Yes in the warning box that will appear.
Method 4 – Clear Pivot Table Cache
If your file contains a Pivot Table, clearing the Pivot Table Cache can substantially reduce file size.
- 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.
Method 5 – Compress Embedded Objects While Uploading
If you have Embedded Objects like PDF, MS Word, MS Excel, MS PowerPoint, and Picture files in your Excel file, compressing them can reduce file size.
Consider the following worksheet containing an embedded MS Word File. Let’s compress this Embedded Object to reduce our Excel file size.
STEPS:
- Right-click on the embedded MS Word file.
- Select the Document Object option.
- Click Open.
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 the Picture Format tab will appear on the ribbon.
- Go to the Picture Format tab and click the Compress Pictures option.
Click the image for 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 Resolution to E-mail (96 ppi): minimize document size for sharing.
- Click OK.
Click this image for 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 for a detailed view
- Return to your Excel worksheet.
- Select the Embedded Object and press Delete to remove it.
- Select the cell where you want to insert the object.
- Go to the Insert tab and select the Object option from Text group.
Click the image to get a detailed view
The Object dialog box opens
- 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.
Method 6 – Delete Unnecessary Data or Sheets
STEPS:
- Select unnecessary sheets by clicking on their tabs while holding the Ctrl key.
- Right-click on the selected sheets.
- Select Delete.
Method 7 – Remove Unnecessary Conditional Formatting
STEPS:
- Select the cells that contain unnecessary Conditional Formatting.
- Go to Home tab.
- Select Conditional Formatting >> Clear Rules >> Clear Rules from Selected Cells.
Method 8 – Only Keep Used Ranges in Worksheets
STEPS:
- Select the first unused row.
- Press Ctrl + Shift + Down Arrow (⬇).
- Right-click on selected rows.
- Select Delete.
Method 9 – Remove Needless Formulas
The marked cells contain the same formulas.
STEPS:
- Select the value and press Ctrl + C to copy it.
- Right-click on the cell where you want to paste the value.
- Select Values from Paste Options.
Method 10 – Remove Watches
STEPS:
- Go to the Formulas tab.
- Select Watch Window.
- Select all the watches.
- Click on Delete Watch.
Method 11 – Delete Unnecessary Hidden Data
STEPS:
- Select the 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 more of 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, our 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, you can either 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.
Example 1 – Compress an Excel File to a Zip File
STEPS:
- Select the file.
- Right-click on it and select Send to.
- Select Compressed (zipped) folder.
The file size is reduced.
Example 2 – Save File in Binary Format
STEPS:
- Go to the File tab.
- Select Save As.
- Select Excel Binary Workbook (*.xlsb) as File Type.
- Select Save.
After saving the file in binary format, our Excel file size has reduced from 53 KB to 49 KB.
Example 3 – Optimize Used Formulas
Complex formulas (e.g. nested IFs, array formulas, etc.) can increase the size of your Excel file. You can use alternative simple formulas, and/or optimize the used formulas by avoiding unstable functions.
How to Reduce Excel File Size Without Opening the File
STEPS:
- Select the Excel file.
- Right-click on it and select Properties.
- Select Advanced.
- Enable Compress contents to save disk space.
- Click OK.
After applying this method, our 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 them handle most Excel file formats. Here is a list of tools that you can use to compress your Excel files online:
- 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 several reasons behind larger file sizes, including:
- Inserted 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 the Excel file.
- Saving in binary format.
- Compressing images.
- Removing unnecessary formatting.
- Deleting the Pivot Table cache.
Download Practice Workbook
Reduce Excel File Size: Knowledge Hub
<< Go Back to Excel Files | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.