How to Reduce Excel File Size
Microsoft Office suite has become an essential software package for companies in most business environments. MS Excel, also called Microsoft Excel, is a software program of Office suite that allows us to use tables (with rows and columns) to put our data and apply various operations using different excel features. Excel has become irreplaceable software for most companies and individuals.
Because of its impressive features, excel is used widely around the world. However, most people experience difficulties with the excessive size of the excel files. The excessive file size can cause various issues, i.e.:
- Slowing down the entire system or operating system by using huge resources (such as memory and CPU)
- Long saving times, further negatively impacting productivity when the file is on a network and requires frequent data updates.
- Difficulty in sharing files due to heavy size, exceeding the limits of e-mail or other platforms’ attachments limits.
Therefore, it is important to keep the excel file size as low as possible. This will increase the overall performance and the productivity of the work processes. There are some tricks and methods that we can leverage to reduce the size of excel files to the maximum extent.
In this article, we are discussing two different methods that can be used to reduce the size of excel files to the maximum extent. They are:
- Reduce Excel File Size Manually
- Reduce Excel File Size in One Click
Caution: Always keep a copy of the original file before trying to reduce the excel file size. This will help to recover the data in case anything goes wrong. It is always recommended to create a copy of the original file and try to work on this duplicate file only. It will also help us to compare the results between the files before trying the methods and after trying the methods of reducing the file size.
Let us discuss both the methods one by one:
Method 1: Reduce Excel File Size Manually
Although this method can sometimes be lengthy, it ensures data security and can have large implications towards reducing Excel file sizes. The method includes several tricks or techniques that can help anyone reduce the Excel file size. We don’t need to follow all tricks; sometimes, the following few tricks may be sufficient for further process. Let us discuss each of the major ways to reduce the file size for Excel:
Remove Unnecessary Worksheets
We know that excel files typically contain multiple sheets (or worksheets). In case some sheets are not required to be sent, or if they are empty, we can remove those worksheets. This can be beneficial in reducing the excel file size. However, the size reduction may be minimal to huge based on the data present in particular worksheets. To remove unnecessary worksheets, we need to right-click on the sheet name and then click on the ‘delete‘ option from the menu list.
In our case, we have two unnecessary sheets. It looks like the following image:
Remove Hidden Worksheets
Sometimes, excel files may also contain some hidden sheets. Although it is a great feature of MS excel to keep some sheets private, it may sometimes include unnecessary sheets. Therefore, it is good to unhide such worksheets and ensure whether they are important or useless. To find the hidden worksheets, we need to right-click on the sheet name and then click on the ‘unhide‘ option from the menu list.
Once we click on the unhide option, a new window will open, which will display all the hidden worksheets.
Next, we need to unhide all sheets by selecting the worksheet and clicking on the ‘OK’ button. We need to repeat the entire process again and again until we unhide all sheets. After that, we can check each worksheet one by one. Since we have all the worksheets visible, we can delete unnecessary worksheets by following the previous method discussed above.
Split the Workbook
In case there are multiple worksheets in an excel file, and all of them are important, we can split them into separate files. This will help in sharing the huge data easily. Suppose we have a few worksheets inside the workbook ‘Data’. Each sheet consists of different data, i.e., Data1, Data2, and Data3.
We can split these three different sheets into three different excel files. We need to right-click on the sheet name and then click on the ‘move and copy‘ option from the menu list.
Next, we need to select the ‘new book‘ option from the drop-down list and click on the ‘OK’ button. After that, the entire data of a particular worksheet is moved to a new excel file. We can save this new excel file accordingly.
We must repeat the entire procedure for all the worksheets to move the data into new excel files. By doing this, we will get multiple excel files with relatively small sizes instead of one huge file.
Remove Hidden Rows and Columns
Sometimes, there can be several hidden rows or columns or even both inside the excel worksheets. Additionally, these rows or columns may contain some unnecessary data, including the texts, images, etc. We can identify hidden rows and columns by observing the serial numbers of rows and columns. Also, there will be double-lines where the rows and columns are hidden. It generally looks like the following image:
It is a great idea to unhide all such hidden rows and columns and check the corresponding data to ensure whether it is necessary or not. To unhide these rows and columns, we need to select particular rows or columns, right-click on them, and select the option named ‘unhide‘ from the menu list.
We can also click twice on the double-lines in the sequencing area of hidden objects to display their respective rows and columns.
Remove Data Formatting
Excessive amounts of data formatting can also be a reason for a little bit of an increase in file size. The term ‘data formatting’ typically refers to styling features such as font styles, colors, type, and weight. This can also include background colors, table borders, underlines, etc. Sometimes, the empty cells have some such formatting, which does not even make sense. Therefore, it is crucial to analyze the excel worksheets and find unnecessary formatting to remove them. However, the impacts on file reduction are mostly minimal.
To remove data formatting from an excel file, we need to select the entire data using the keyboard shortcut Ctrl + A, or clicking on the select all button.
Next, we need to click on the ‘Home‘ tab and click on the ‘Clear‘ from the ‘Editing’ tools. It will open up a list with more options. Then, we are required to click on the ‘Clear Formats‘ from the list.
After clicking on the ‘Clear Formats’ button, all the formatting or styles will be removed without making any change in the data.
Remove Conditional Formatting
Conditional formatting can also increase the excel file size a bit. Therefore, it is recommended to remove all unnecessary conditional formatting to reduce the excel file size to some extent. The method discussed above by using the ‘Clear Formats’ button will remove all the formatting. But if there is a need to remove only the conditional formatting and leave the other formatting and data intact, we can also do it. It will be beneficial to reduce the excel file size with minimal results.
To remove the conditional formatting from an entire worksheet, we need to click on Home > Conditional Formatting > Clear Rules > Clear Rules from the entire sheet. Besides, we can also clear conditional formatting from particular cells by selecting only those cells and then clearing rules accordingly.
Unlike the previous procedure, this method removes the conditional formatting only.
Remove Unnecessary Formulas
If we have an excel file with so many formulas inside it, we need to check whether we need them. Also, we may have used the formulas to calculate the past values, but we may not be going to use those formulas to make more calculations in the future. In such cases, it is a great idea to remove those formulas from the excel worksheets and convert the previous results as normal values. By doing this, we will not be changing the desired data but reducing the excel file size.
Before removing unnecessary formulas from the file, we need to find particular cells where formulas have been used. First, we need to go to Home > Find & Select (From the Editing Section) > Go to Special. We can also press the F5 button on the keyboard and then click on the ‘Special’ button.
A popup window will appear, which looks like the following image:
Here, we need to click on all the checkboxes given under the ‘Formulas’ and click on the ‘OK’ button. By doing this, we make sure to highlight all the elements related to formulas. Next, we are required to select all the highlighted cells where formulas have been applied. We can copy the selected data by using a keyboard shortcut Ctrl + C.
Now, we need to paste the data back to corresponding cells but without the formulas. If we use the shortcut Ctrl + V to paste the data, it will also paste their respective formulas. Therefore, we cannot use this shortcut. We only require the values, and therefore, we need to go to Home > Paste (From the Clipboard Section) > Paste Values > Values.
This will only paste the cells’ values, which will slightly reduce the overall Excel size.
Compress Image
Images can sometimes be responsible for the excessive size of excel files. In particular, images take more space as compared to the texts or digits. So, if there are multiple images in the excel file, then compressing and replacing the images can help save a lot of space. One can compress the images using two ways:
- The first way is by using a Photoshop tool and reducing the resolution or DPI of the image.
- Another way involves the use of external applications. One such commonly used application is “tinypng“. Using this application, we can drag/drop or upload image files and reduce the image sizes without losing the noticeable image quality. It works online via web browsers, allowing the compression of up to 20 images (.png or .jpg only) simultaneously. Additionally, each image file must be of a maximum of 5 MB only.
In our case, the size of the image file named ‘Excel Analytical Data.jpg’ was reduced from about 912 KB to about 200 KB. The reduction in image file size is more than 70%. Therefore, compressing multiple images used in an Excel file can help reduce the Excel file size to a great extent.
Convert (Save As) Excel Files to Binary Format (.xlsb)
Generally, excel files are saved in XLSX format, which is an open XML spreadsheet file format. This format mainly stores excel files in different XML files and compresses all files with an xlsx extension. To check XML files inside an excel file, we can change the XLSX file’s extension to a ZIP extension by renaming the file. Next, we can extract the zip file to get all the XML files. The XLSX format is mainly helpful for the third-party software to export the data from XLSX files easily. However, it generally increases the size of the excel file.
However, we can reduce the file size by saving our spreadsheet as a binary workbook (.xlsb format). For general purposes, the binary format is sufficient and can help save the file size up to 50%. Furthermore, the read and write speeds in the binary workbook are comparatively faster.
To convert an existing excel XLSX file to XLSB format, we need to open a file and then go to File > Options > Save As. On the next screen, we need to click on the option named ‘Save as Type‘. It will open a list of various supported formats for excel files, such as (following image):
Here, we need to select ‘Excel Binary Workbook (.xlsb)’ and click on the ‘OK’ button to save the file. This will save a copy of the existing excel workbook as a binary workbook; however, they (binaries) cannot be open outside the Office Suite.
Remove Pivot Cache
Every time we create a pivot table in excel, an additional pivot cache is created that contains a copy of the original table in a convenient form to be used for other applications. Whenever we make changes to the pivot table, Excel uses the cache instead of the original table for faster performance. Additionally, if we make changes to the original table, excel modifies the pivot cache accordingly.
When we create pivot tables within the excel worksheets, the size of the excel file increases significantly. The following files display the difference between an excel file without a pivot table and an excel file with a pivot table:
There are few ways we can reduce the excel file size in this case:
- Delete Pivot Table: If there is no need for a pivot table, it is a great idea to delete the entire pivot table. This will automatically delete the pivot cache data, and the size of the file will be reduced.
- Delete Pivot Cache: If we need a pivot table in our excel file, it is a great idea to delete only the pivot cache and reduce the excel file size. To delete only pivot cache, we need first to select the cell where the pivot table is present. Next, we need to go to PivotTable Tools > Analyze and select Options in the PivotTable group. In the PivotTable Options window, we need to click on the ‘Data‘ tab and make the changes according to the following image:
After making the changes, we need to click on the ‘OK’ button, and the pivot cache will be removed. After that, we can compare the changes in excel file sizes:
There is a slight decrease in the Excel file size after removing the pivot cache, as shown in the above image’s first file.
Compress inside ZIP File
Sometimes, compressing an excel file inside the zip can also be helpful. Suppose we are required to send an excel file via e-mail, and the size of the file is slightly exceeding the limit of attachments. In this case, compressing the excel file by zipping it can be a good idea before sending. For creating a zip file, we need to right-click on the particular excel file and choose to Send to > Compressed (zipped) folder. After that, we can enter the required name for a zip file.
The built-in windows application is not so powerful, and therefore, it can only help us compress a file to some extent. The following image displays an original excel file (1430 KB) and a compressed zip file (1412 KB) of excel.
Besides, some powerful third-party compression software can help reduce the size of a compressed ZIP file significantly. Examples include WinRAR and 7zip.
Method 2: Reduce Excel File Size in One Click
This method is very easy and can reduce the excel file size quickly. Technically, this method is not a one-click method. The method includes few simple steps that are much easier to follow than the first method we discussed above. Many online websites and offline software can reduce the excel file size to the maximum extent. One such popular option is a ‘wecompress‘.
Wecompress is a software/tool that works over the Internet and allows us to upload different files and reduce file sizes. Besides, there is also a desktop version of this tool that can work offline. However, the free version only allows the compression of files up to 50 MB.
Here, we are discussing the steps of using the ‘wecompress’ tool via web browser:
Step 1: First of all, launch the web browser and navigate to the official website of ‘wecompress’ using the link given below:
https://www.wecompress.com/en/
It will open up a web page, which looks like the following image:
Step 2: Next, drag and drop the excel file directly into the web page, or click on the plus icon to select the file using the file explorer. Once the file is selected, the tool will automatically upload the file and start compressing it. It may take time based on the size of the excel file.
Step 3: Once the above step has successfully finished, there will be a download button. We need to click on this download button and save our compressed excel file into our device. In our case, the original size of the file named ‘Demo File.xlsx’ was 1.40 MB, and it is reduced to 614 KB after using ‘wecompress’.
Caution: We do not take responsibility for the data security of Excel files when using the ‘wecompress’ tool to reduce file size. It is highly recommended to go through their privacy policies or terms and conditions to understand how ‘wecompress’ uses data from Excel files uploaded to its server.
