Top 11 Ways To Reduce Excel File Size Without Deleting Data

Summary:

Today through this post you will get to know what’s the reason for your oversized Excel file. or what’s making your Excel file size too large. Moreover, the blog also emphasizes on how to reduce Excel File Size without deleting data.

There are many causes that can help an Excel file to become huge, sometimes Excel spreadsheet becomes so huge while it actually containing very little information.

A large-sized Excel workbook may generate lots of issues while dealing with the file, such as file will respond very slow, take more time to save any changes, take time to upload, and more.

What’s Making Your Excel File Size Too Big?

The main question is “What part of an Excel file causes the big file size. If you don’t know what’s making your Excel file size too big then keep reading the following facts and you will surely get the answer to your query.

Huge information: Generally a worksheet having more than 100,000 rows and many columns demands a large memory to store.

Pivot Tables: Yes, the Pivot table may also affect the file size. Pivot Tables store the original data hence in each of the Pivot Table have its own data set.

Add-ons and plugins: Some plug-ins significantly drop down your Excel workbook performance. If you are running or installing several plug-ins in your Excel workbook then stop doing that.

Formatting: In case the sheets are formatted though cell ranges aren’t in use then the file size might increase.

Hyperlinks: Sometimes excessive external sources links cause frequent crashing of your Excel application.so find out whether your Excel file is getting crashed or other files linked to it causing such a problem.

Media files: If your Excel file containing images/pictures/logo then it can turn your small excel file into a big one.

Compatibility issues: Some attributes of Excel won’t work well on all Excel versions. The chances are high that your excel file is undergoing some compatibility issues. So you need to fix it first.

Sometimes Excel file’s Meta-data and other hidden contents may also boost the file size.

So, there are several causes of a big MS Excel file size hence we have to apply different tricks to reduce it. The solution may depend on the contents of the Excel workbook if you are not sure that which one is affecting your excel file then you should apply them one by one.

Let’s start applying the below-given tricks one by one to convert a large Excel file into a compatible size by reducing Excel file size.

How To Reduce Excel File Size?

Method 1# Remove Cell Formatting

While creating an Excel file we apply several formatting over the spreadsheet, hence deleting formatting will help you to reduce size of Excel file and boost the spreadsheet performance.

Steps to clear the Excel formatting

Step 1: First, choose the range over which you want to clear the formatting.

Step 2: Then go through the: Home Tab > Editing Group > Clear > Clear Formats

If the excel spreadsheet containing lots of text then it is the best option.

Method 2# Reduce The Size Of The Image File Added In Microsoft Excel

Steps to compress or change the resolution of the image. Compressing doesn’t affect the quality of your excel file. If you don’t want every single pixel in a picture then you can go with the compress option and make the file size smaller.

• Open your excel file that contains an image or logo and then choose the picture or pictures for compressing.
• Now, go to the Picture Tools -> Format tab, and in the Adjust group how to reduce Excel file size you have to click on the Compress Pictures.

Note: If you are unable to see the Picture Tools – Format tab then confirms that you have selected a picture. Double-click on the image to choose it and open the Format tab.

• If you only seeing the Adjust group then the Compress Pictures button may appear without a label.
• In order to decrease the size of a specific image and leave all of the other images present in the document, mark the Apply only to this picture check box.
• If you unmark the Apply only to this picture checkbox then it will override any previous modifications you have done for other images present in this document.
• Now, go to the Resolution and then select the resolution that you want and click OK.

Method 3# Convert File type to Binary Format (XLSB)

By converting an excel file to the binary format can reduce size of Excel file with ease. So, follow the given steps and save an Excel file in the XLSB format to decrease the file size.

1: Click on the File tab and then go to the Save As option.

2: Now, click on the Browse button to change the file type.

3: From the Save As dialog box, you need to pick the Excel Binary Workbook (.xlsb) file type. Click Save.

 

Method 4# Remove Unnecessary Formulas

Having lots of complicated formulas can affect the file size of an Excel file, so in this method, we will try to remove unwanted formulas from an excel workbook.

Sometimes when you have a data set and don’t need the formulas in it then it is better to replace these formulas into values. Follow the below-given steps to do this:

• Open the Excel file and press the ctrl + A keys to select the entire worksheet/dataset then press the F5 key.
• Open the Go To option and click on the ‘Special’.
• Select the Formulas and then click OK.
• Now, you have selected all the cells that have a formula.
• After it, follow the below steps to convert the selected formulas into values:
•  Press the ctrl + C to copy the selected cells.
• Then click on the Home tab, and from the Clipboard group, click on the Paste.
• After that click on the Paste Value icon.

So, all the cells with formulas now get converted into the values.

Method 5# Optimize The Used Formulas

After removing the unwanted formulas, now we will optimize the used formulas by using the following steps:

Skip using a volatile or unstable function such as –
TODAY, NOW, INDIRECT, RAND, RANDBETWEEN, CELL, OFFSET, INFO.

You need to replace the VLOOKUP, HLOOKUP and LOOKUP function with INDEX & MATCH

You should also replace the nested IF functions with any of the below given:
REPT, INDEX & MATCH, SUMIF(s) or Boolean logic

Method 6# Compress The File (ZIP IT)

This method is a little different, using the zip file option you can compress the excel file with ease. By compressing an Excel file, you can reduce file size of about 10-15%.

Steps to Zip an Excel file:

Step 1: First you have to right-click on the Excel File that you want to zip

Step 2: Now, take your mouse cursor to the Send to option and click on the ‘Compressed (zipped) folder’ option.

That’s it, after creating a zipped file share it with others and the receiver will unzip the file to access the Excel file.

Method 7# Delete The Unused Cells

In order to delete the visible unused table, columns and formatted cells follow the below-given step.

• First, you have to select all the rows to present below your last row having content.
• Then Press Ctrl + ‘–’ from the keyboard to delete the rows.
• After it, click on the rubber button present at the right-hand side of the Home ribbon.
• And then click on ‘Clear All’.

• You have to repeat the above steps on every worksheet as well as for the columns.
• At last, save the workbook and check that the file size is reduced or not

Method 8# Reduce Condition Formatting From Excel File

• To do this, you have to choose the entire worksheet by pressing the Ctrl + A keys from the keyword.
• Now, go to the Home / Conditional Formatting / Manage Roles
• At last, select the irrelevant roles and erase the Rule.

Method 9# How To Reduce Excel File Size Without Opening

In this method, you will get to know how to reduce Excel file size without opening. This method is different from the zip compressing,

This trick is not present on Microsoft Excel but this option is present on every Windows system to compact the size of various files that include Microsoft Excel.

• In order to compact your Excel file, you have to find your excel file using Windows Explorer and then right-click on the file.
• Choose Properties from the context menu and then select the Advanced Button
• At last, choose the “compress contents to save disk space…”

Method 10# Reduce The Size Of Pivot Tables

As we have already discussed that the Pivot Tables consume a lot of memory and may increase the file size. So, we will try to reduce the size of the pivot tables.

There are mainly 3 options to reduce the Size of Pivot Tables:

Option 1: Sometimes, Pivot Tables are created to check data or seeing an overview. If you don’t need these Pivot Tables in your workbook, then remove the unused pivot table.

Option 2: Instead of creating several Pivot Tables having the same source code you should copy and use them where you need. Through this, you can share the same data cache.

Option 3: Avoid saving the source data of the Pivot Table with the file. To do this, right-click on the Pivot Table and then go to the Pivot Table Options. Now, from the data tab unmark the tick from ‘Save source data with file’.

Method 11# Check And Delete Unnecessary Hidden Cells

Hidden cells may also add some extra burden to your Excel file. Unwanted hidden cells can boost the size of the Excel file. Microsoft cells get hidden accidentally while working on the rows or columns of the Excel worksheet.

If you find out that your file contains unwanted hidden elements then you need to unhide the rows and columns of your Excel worksheet and analyze you need them or not. Delete unwanted hidden cells to reduce Excel file size.

What Is The Maximum Excel File Size?

If you never want to face this oversized excel file issue again in the future then it’s important to have the right knowledge of maximum Excel file size limit.

To know this, you must read this informative post specifically written on Excel specifications and limits.

At last, I also want to conclude that make a proper backup of your Excel data first before performing any of these methods to reduce Excel file size.

So that if in case the, fixes you have applied haven’t worked then at least you have the option to revert back your original data.

Conclusion:

All the above-given methods are very effective and will help you to reduce size of Excel file.

So, it is hoped that after applying all the above discussed 11 methods to reduce Excel file size you can easily troubleshoot Excel “Maximum Memory or File Size Exceeded” error. If you have any queries or suggestions about this article or suffering from any other kind of issue while using the Microsoft Excel file then share your issue in the comment section of this blog.