04 December, 2006

Reduce the Size of an Excel File

I have been working with an Excel file for about 18 months -- each month I link to a monthly Excel data file. This gives me data in one file that I can use to trend many different segments. I am not sure when, but this file had ballooned to over 3 MBs. Finally this month when I tried to upload it to our intranet, I received a file size limit exceeded message.

I searched and searched, and could not find any thing that would compress or clean up the file. Finally I went to Google and found a solution. The credit goes to OzGrid Business Applications. Here is a link to the entire article, and below is what I had done to reduce the file size.
  • The first thing of course is to make a backup... just in case
  • I then performed the following on each worksheet (only 2 in my case):
    • Select a cell and press F5
    • Click the Special... button
    • Click the Blanks radio button
    • Click OK
    • Select Edit | Clear | All
    • Manually go to the last row in the worksheet (do not use Edit menu shortcut)
    • Select the entire row
    • Press Ctrl + Shift + [down arrow]
    • Select Edit | Clear | All
    • Remember to repeat for each worksheet
  • Save
In my case, the file went from over 3 GBs to less than 200K. If this does not work for you, OzGrid does offer some other possible things to try.

7 comments:

Jennifer said...

Thanks so much for posting this!! I was just having this issue and followed your steps. My file reduced from 5M to 1.5M which is what I started with last week but as I added more stuff, I must have added all the extra blank rows.

Just a nice check would be (got it from some other site) to do CTRL-end and the selected cell should be at the last line of your workbook. Mine went to the 65555 or whatever is the max of the worksheet so it was clearly filled with blanks which were bloating the file.

Thanks!!

Chris Todd said...

Glad it was helpful and thanks for the tip.

The Middle Man said...

Thank you very much for this. My spreadsheet went down from 3.4MB to 40KB.

Sam said...

I just tried this, but since I have merged cells, it also selected those as blanks, and erased everything. So be careful...

Vijay said...

Thanks a lot, helped me like anything..my page size was 7.5MB reduced to 350KB...

Raja said...

Thank you. I did what you said and my file size reduced from 10 MB to 182 KB.

Another comment I might add is that if you are using vlook up and other commands, it is wise to do a paste special values to reduce the size.

Anvita said...

Thank you very much. I was having too much trouble because of this giant excel file I have - your trick helped me bring its size down to 4 MB from 20 MB.