Attempting to forward an oversized Excel document via email can be a stressful experience that may compromise not just its quality but also its overall experience opening it. If faced with such a scenario, it is best to reduce your Excel file size for swifter operations and smoother sharing experiences among members of your work team or clients. This becomes all the more critical when dealing with files over 50 MB, making sharing tricky! In light of these concerns, here are some tips on how to reduce the size of an Excel file.
What’s Making Your Excel File Size Too Big?
- Formatting: Excel sheets have the astounding ability to accommodate many cells that could even number in millions. What may not be immediately evident is that adding formatting, even with no data inputted, can lead to Excel files being excessively big. Identifying these particular formatted cells is tricky because their appearance matches that of unformatted ones.
- Pivot Tables: By default, the Excel file saves the data needed for your pivot tables, which can get quite big, increasing the size of the Excel document.
- Huge information: When you have an Excel document with vast amounts of information, naturally, it will increase the size of the Excel file.
- Media files: If your Excel document contains many high-quality images and other media files, it can increase the size of the Excel document.
How to Reduce the Size of an Excel File
Here are some of the working solutions that can help you reduce the size of your Excel files:
- Save Your Spreadsheet in Binary (.xslb) Format
- Decrease the Number of Worksheets
- Reduce the Resolution and Size of Your Images.
- Compress Images
- Do Not Save the Pivot Cache With the File
Always have a backup of the original file before making the changes using the above solution. In case something goes wrong, you can also repair the Excel File.
1] Save Your Spreadsheet in Binary (.xslb) Format
By saving your spreadsheet as a binary workbook (.xslb) compared to the standard version (.xslx), you can reduce the size of your file. The default format is based on XML, which is significant if you use your data with third-party applications because XML is an open standard. The binary format, however, saves to a smaller file and is adequate for most tasks. So, this is how you save your spreadsheet in binary (.xslb) format:
- Open your Excel spreadsheet and click on File > Save As. Choose the destination where you want to save the file.
- In the Save As window, click on the drop-down menu next to Save as type, choose the Excel Binary Workbook (*.xslb) option from the list of options, and click Save.
Thus, your spreadsheet will be saved in binary format.
Related: Fix The File Is Too Large for the Destination File System Error on Windows
2] Decrease the Number of Worksheets
Delete worksheets with data from the spreadsheet if you do not use them and they do not contain any necessary formulas. Your workbook’s file size will increase directly to the number of data points it contains. Your file size will be decreased if unneeded data is removed. This is how you delete a sheet in Excel:
Select the sheet, then click the Home tab > Delete > Delete Sheet.
3] Reduce the Resolution and Size of Your Images
In this method, you can reduce the resolution and size of the images in your Excel document, resulting in a reduced Excel file size. This is how you reduce the resolution and size of the images in your Excel file:
- Open your Excel document and go to File > Options to open the Excel Options window.
- Click on the Advanced option from the left pane. Next, scroll down and locate the heading Image Size and Quality.
- Next, click on the checkbox next to the Discard editing data option to enable it.
Note: This option deletes the previously used information to change an image and returns it to its original state. Remember that you cannot restore the image if you delete the modified data.
- Confirm that the option Do not compress images in file is not enabled.
- Click on the drop-down menu next to the Set default target output to option and choose a resolution of 150ppi or less from the Default resolution list. You will not need a resolution higher than that.
After you have done the steps mentioned above, the size of the images in your Excel document will be reduced, which automatically reduces the overall size of the document.
4] Compress Images
This method will compress the chosen images in the Excel document and select the document resolution option. So, this is how you compress an image in an Excel document:
- Click on any picture in your document. This will open up the Picture Tools tab. Next, click on the Format tab under Picture Tools. In the Adjust group, click on the Compress Pictures option. In the Compress Pictures window, make the following changes:
- Under Compress options, enable the Apply only to this picture option to make the changes only to the chosen picture. If you want the changes to be applied to all the pictures in the document, disable the Apply only to this picture option.
- Enable the Delete cropped areas of pictures option to delete the cropped area of the picture. Once you enable this option, it will permanently delete the cropped area of the picture, and you will not be able to undo it.
- Lastly, enable the Use document resolution option under Target options and press OK to complete compressing the images in the Excel document.
5] Do Not Save the Pivot Cache With the File
If your spreadsheet has a pivot table, you can reduce the file size by having the data cache refresh when you open the spreadsheet rather than saving the pivot table source data cache with the file. So, this is how you refresh the data:
- In your Excel spreadsheet, click on any cell on the pivot table.
- This will open the PivotTable Tool tab. Next, click on the Analyze tab under it and locate the Pivot Table group. Click the drop-down arrow next to Options and choose Options from the menu.
- In the PivotTable Options window, click on the Data tab. Under PivotTable Data, uncheck the Save source data with file option and check the Refresh data when opening the file option. Click OK.
The PivotTable cache will be cleared, thus reducing the size of the Excel file.
Note: If you are utilizing several pivot tables, you can copy the entire pivot table (or just the tab) and paste it into another location, and it will still use the same data cache. You will overload your file by creating several caches of the data if you create many pivot tables but select the data each time before choosing Insert > Pivot Table.
6] Remove Unused Rows & Columns
You can reduce the size of your Excel file by deleting any unused rows and columns in your document. Here is how you remove unused rows and columns in your Excel document:
- Select all blank columns on your Excel sheet by clicking on the letter of the first column.
- Next, hold down the Shift key and click on the letter of the last blank column. After that, right-click the selected columns to bring up a pop-up menu and choose Delete.
- Similarly, to delete the blank rows, click on the number next to the row where the blank row starts, press the Shift key and click on the number of the row where the blank row ends, right-click, and choose Delete. All the unused rows and columns will be deleted.
Conclusion
Many have an Excel file that is too big. Everything takes twice as long because opening or saving them takes minutes. Excel files for financial models may become much more significant as their complexity and number of components rise. So, you can use all these mentioned methods in the article to reduce the size of bulky Excel files, which will result in smooth dealings of Excel files.
Why does the Excel 64-bit version take less space compared to the 32-bit?
With 64-bit programs, the app and any add-ins running in the same process can share up to 128 TB of virtual address space. You may only receive 2 GB of virtual address space for 32-bit apps, which is frequently insufficient and can lead to the app crashing or malfunctioning.
