Press "Enter" to skip to content

7 Pro Techniques To Reduce Excel File Size (Working Methods)

Kaustubh Patel 1

I am having a large excel file consisting of lots of data, formulas, formatting, and images in it. And, it’s a known fact that excel will become painfully slow and heavy when file size is too large.

Next, it becomes to difficult to work on such a heavy file. In fact, there is a higher chance that such an excel file will get corrupted while working on it.

And, since MS Excel is not a web-based tool, in almost all the cases, you will require to send the file through the email server.

In such a case, it becomes almost impossible to share such heavy files over email.

Hence, a question may arise, how to reduce excel file size?

If you are looking for the same answer then you are in the right place. This article will share the six best ways to reduce the excel file size (without going online).

So, What are waiting for? Let us discuss those techniques or methods below.

6 Pro Techniques To Reduce Excel File Size

There are many online tools or software claiming that it will reduce the file size to a great extent. Yes, there is no doubt about that. Those tools or software works like a charm.

But, it will use the same techniques, which I am going to share below. The only difference is- In online tools or software, it can be done automatically without your interference.

But, on the other side, to use them, you will require to download the software or upload file on an online server. Which in itself is a very hectic task.

Hence, I strongly suggest, to avoid such kind of software or online tools. Instead, apply the below hacks to reduce the excel file size.

#1. Convert Into Binary Format

This is the first and the easiest way to reduce the excel file size. In this technique, all you need to do is- Save as your file in the binary format that is ‘.XLSB’.

That’s it. It will reduce your excel file size to a great extent.

But, before saving the file in ‘Binary Formats’, I would suggest you to please refer the limitation or disadvantage of binary format excel files below.

  • Compatibility issues in using data with other software that require XML data instead of Binary.
  • It is difficult to identify whether the binary format includes harmful Macro or not? Hence, it is not advisable to download XLSB excel file form harmful source or un-trusted websites.
  • XLSB is not supported by other Office Suites like Open Office etc.

Above all are the various demerits of using the binary format for your excel file.

Next, if you properly read the above limitations than you will observe that there are no major disadvantages in using binary format.

But, have you ever wonder? Why Microsoft Excel has .XLS as default format instead .XLSB?

If you have known the answer, then please do share your answer in below comment box?

#2. Paste Unnecessary Formulas as Value

If your file includes formulas then this is the second technique to reduce the file size. In this method, all you need to do is- Copy and Paste your unrequired formulas into values.

For example, you can copy and paste the v-lookup formulas into values.

The easiest way to do this is by using the ‘Go to Special’ option available in the excel. The below process will clarify this.

  1. Open Excel file and select entire worksheet (or else Press Ctrl+A)
  2. Next, press Ctrl+G to open the ‘Go to’ window
  3. Press Alt+S for the ‘Go to Special’ window.
  4. Select the appropriate options ( as applicable) from ‘Formula’ and Press Ok
  5. Now, all the cells containing formulas will get selected
  6. At last, Press Ctrl+C (to copy) and Alt+H+V+V to paste copied content as values.

The below images will clarify all your doubts. But, if in case, you still have any questions or problems in using the above process then feel free to contact us directly or share your message in below comment box.

#3. Clear Data Formatting

This is the biggest reason for the increase in the file size of any excel. Though your file includes less data but an over formatted file with definitely increase size.

Hence, if you running difficulties in loading or using the file, then clear data formatting immediately. You can easily do this through the ‘Clear Format’ function available under the ‘Home’ tab of your excel ribbon.

Always Remember, ‘Keep It Simple’, is the key to any good excel file.

#4. Remove Watches In Excel File

Watch window is a window that floats along with your worksheet and let you see the selected cells from anywhere in the excel. In simple words, this will help you to see how the particular change in the cell will affect the watched cells.

Watch Window in MS Excel

Watch Window is generally used by the professional while working on a number of large files including lots of data in it.

It is strongly advisable that once you are done with your file, the best practice is to remove /delete it. Trust, this will reduce the file size to a great extent.

#5. Compress Images In The Excel

Whether it is your hard drive or excel sheet, Images and Videos will always consume space. Remember, Excel is a spreadsheet program, ideally it should include any images in it.

But, if in case, you want to add images to your excel file then please ensure those images are well compressed in respect to its file size.

You can compress the images either offline before inserting it within the excel file or use an in-built ‘Compress Image’ option available in the excel program.

To use in-built option, all you need to do is select the picture or image and click on ‘Compress Pictures’ from Picture Format Tab ( as shown in below image).

Compress Image In MS Excel

#6. Delete The Entire unused Columns and Rows

Here, it is very important to understand the meaning of term’Delete’ first. By word ‘Delete’, we really don’t mean to say remove the unused rows and columns permanently.

Actually, this is even not possible in excel. You can’t delete the unused columns and rows from the excel file. Instead, it can be only hidden.

But wait, here you even don’t need to hide it.

Confused? No Problem, the below paragraphs will clear your all doubts.

In an excel file, blank cells consume space (assuming it contains data in it like Space bar). This will increase the size of your excel file.

The best way to avoid this problem is to check the last used row and column of the worksheet. This can be easily checked using shortcut key Ctrl+End.

Ctrl+End will take you to the last used cell in the worksheet. Now, if in case your last cell is completely out of your data table, then you need to delete the unused rows and columns for reducing the space consumption.

In simple words, all the rows and columns ( that are being not used) should be deleted from your file.

#7. Using ZIP (Compressed File) Technique

This technique is a little bit technical in nature. For a normal user, it is difficult to implement it.

But, don’t worry. Hereby, I am tried my best to explain this technique to you in the easiest language. Below, I have shared the step by step instruction to implement this technique.

  1. Change the extension of your Excel file to ‘.ZIP’. This will change your excel into a ZIP folder.
  2. Now, extract the ZIP file into the folder.
  3. Open the zipped folder and go to xl > worksheets

That’s it. In the Worksheets folder, you will find the XML versions of your all workbooks.

Next, you will observe that the size of the XML file(s) are less than the original excel file.

Now, open the relevant XML file through the ‘Open With’ option and then ‘Save as’ the data into a new workbook.

That’s it. This process will also reduce the size of your excel file to a large extent.

Over to You

Above all are the various techniques to reduce the excel file size. If in case, these techniques does not work then I strongly suggest to go back and check your excel workbook thoroughly.

There are higher chances that your excel workbook is not properly optimized and contains faulty or corrupted data, formula, or pivot tables in it.

Please note, by term faulty or corrupted, we mean to say under optimized file. For example, If anyone of your formula is not optimized properly then definitely it will consume more space.

In such a scenario, the best way to reduce file size is to start the file from scratch. Forget your old file and start a fresh. Trust me, the new file will be smaller than your current file.

At last, feel free to contact us or leave a message in below comment box, if you face any problem or has any question with regarding to this article. We will be happy to help you.

  1. Devarsh Patel Devarsh Patel

    Dear Kaustubh

    Thanks for writing this article. It will be a great help for people like me who keep working on large files every day. 20 MB is the average file size on which I used to work in my office.

    I am happy that you shared these working methods with us. As mentioned by you in this article, it is really a great pain to share such a heavy file over email.

    Once again thanks for sharing this with us. I really appreciate it.

    Regards

Leave a Reply

Your email address will not be published. Required fields are marked *