Bloated Excel 2010 File Size

[HOOS77]

I have multiple Excel files created in Excel 2007 (all cloned from the same master file) which are used by our managers that unexpectedlyl increase in size when saved in Excel 2010 (as .xlsx files in both cases). The amount of data is insignificant and does not vary that much between the various files. Prior to saving in Excel 2010, the files were about 1.1 to 1.3 mb; afterwards, they ballooned to 11 - 12 mb. There are no pivot tables, no macros and no excessive formatting.

I opened the master copy (blank, no data) in Excel 2010 and immediately saved without making any changes and the file increased from 1.11 mb to 12.4 mb !! Files of this size take much longer to open and save which is a major inconvenience to our managers. Interestingly, when I saved as a binary file, the file size dropped to 1.55 mb.

Have searched in vain on Microsoft and other sites for relevant information and found nothing helpful. There has to be a reason for this, but I'm stumped at this point. Any help would be greatly appreciated!

Thanks.

 

[keithkemble]

Folks,

I have suffered from this bloating many times over the last few years, both in 2007 & 2010.

Currently I am suffering with a file that is now 12.9 M large and when I save it in any alternative except 2010 macro enabled it stays large.
When saved in 2010 macro enabled it shrinks to 1.9m - Yippee.
Could be worth a try.

PS my spreadsheet has multiple sheets and lots of Macros.
Regards

 

[HOOS77]

Thanks for the info. I don't use macros often and there weren't any in the file that blew up in size. So far, saving in binary format is the only thing that has worked. I'm still puzzled by all this because it seems to be a totally random problem which thus far has not affected any other files.

Regards.

 

[xenou]

I doubt there's an answer for you. Stick to .xlsb for now, is all I can think ...

Some longshots:

• Clear all data below and to the right of the last bit of "real data" (clear, not delete) - you don't want unnecessary formatting in these cells
• Round trip through an htm file (save as a web page, then save back as an .xlsb)
• Copy data (not sheets) to a new workbook
• Call Microsoft support (They actually answer the phone - at least that was my experience the one time I tried it)

Note that (as I understand it) .xlsb should be a little bit smaller than .xlsx (though not necessarily better performing). This is due to the way the information is stored in these different file formats. Ordinarily, I don't think this means they should be one tenth the size. But perhaps there are extreme cases where that may be the case. In any case, you needn't worry about .xlsb files - they are perfectly fine. I use the binary format as my default (for no particular reason other than not having to worry about xslx vs. xlsm when I am using macros, as I often do).

 

[HOOS77]

Appreciate the suggestions!

 

[greentea3128]

i'm having the same issue. found an article online..

http://www.technize.net/the-file-size-of-excel-workbook-may-unexpectedly-increase-exponentially/

will give this one a try..

 

[SomeKindaGreekGod]

One thing you can try is copy and rename your file as a .zip (since that's all it really is anyways). Then open it and you should see a list of your sheet names and how big each is. To see what sheet "SheetX" refers to in the zip file, you'll have to open a copy of the excel file and open the VB Editor (Alt+F11)

I'm fighting this issue right now and the three sheets it's reporting as over 10MB each in the zip file only have about 5 columns and 14 rows. I've tried clearing unused rows and columns from each.

Here's the weird part though: If I delete those big sheets, and resave, when I open it as a zip file, it's still saying those sheets are there! I thought before I had fixed it by saving as an XLS then back to XLSX/M, but it's not working today.

 

[SomeKindaGreekGod]

EDIT: Here's what worked for me. I have a workbook that had about 25 sheets, but all less than 5 columns by 50 rows, no formulas, charts, or fancy formatting, and it went from under 1MB to over 7.

So here's what I did. Keep in mind this will only work for Excel 2007/2010:

• Copy your file in case you mess it up
• Rename the copy as a .zip (since that's really all the file is)
• Open the zip and look through the files that look like sheetXX.xml. Look for the big ones. For me, I had 3 that were over 15MB.
• The XML file is saved on only two lines, so if you have Notepad++ and the TextFX plugin, you can use the Tidy option to reindent the XML and make it easer to read.
• If you don't have either of these, just open the XML file in your favorite text editor and search for: codeName="Sheet
• You must do this because the sheetXX in the zip file does not refer to the code name you can see in the VB Editor within Excel.
• Once you have the code name from within the XML file, you can go to your Excel file, check the VB Editor (Alt+F11) to see what the tab name is, then go to that worksheet and select all rows and columns to the end, then Clear All (either with Alt+E+D or with the Clear button in the Editing section of the Home tab).

That solved it for me. I had three bloated sheets and after clearing the empty cells, got my workbook down where it was supposed to be.


Hope that helps!

 

[ernosuomi]

What I was experiencing was that somehow Excel thought that my sheets have data in all the rows. I selected all empty cells and deleted those rows.
Easiest way to do this is to click the column A at the first row without real data. Then press SHIFT + CTRL+END from the keyboard. You should now have a large selection of cells. Then click the selection with the second button of the mouse (right click) and select Delete from the pop-up menu. From the Delete window select Entire row. Now save your workbook and file size should have dropped if you had the same problem as I was having.
If the file size is still too big, repeat this procedure to all your sheets. Hope this helps!

 

[orerockon]

I had a very similar problem and for those searching for a solution I found that if you simply open the file from a ZIP utility, delete the offendign sheets (in my case there were 2 sheets over 600 megs each!) and rename as xlsx(m) it will open right up in excel. Mine wouldn't even open, I assume it had reached Excel's maximum file size or whatever. It even retains the macros.

EDIT: Here's what worked for me. I have a workbook that had about 25 sheets, but all less than 5 columns by 50 rows, no formulas, charts, or fancy formatting, and it went from under 1MB to over 7.

So here's what I did. Keep in mind this will only work for Excel 2007/2010:

• Copy your file in case you mess it up
• Rename the copy as a .zip (since that's really all the file is)
• Open the zip and look through the files that look like sheetXX.xml. Look for the big ones. For me, I had 3 that were over 15MB.
• The XML file is saved on only two lines, so if you have Notepad++ and the TextFX plugin, you can use the Tidy option to reindent the XML and make it easer to read.
• If you don't have either of these, just open the XML file in your favorite text editor and search for: codeName="Sheet
• You must do this because the sheetXX in the zip file does not refer to the code name you can see in the VB Editor within Excel.
• Once you have the code name from within the XML file, you can go to your Excel file, check the VB Editor (Alt+F11) to see what the tab name is, then go to that worksheet and select all rows and columns to the end, then Clear All (either with Alt+E+D or with the Clear button in the Editing section of the Home tab).

That solved it for me. I had three bloated sheets and after clearing the empty cells, got my workbook down where it was supposed to be.


Hope that helps!

 

[scottdrenn]

What I was experiencing was that somehow Excel thought that my sheets have data in all the rows. I selected all empty cells and deleted those rows.
Easiest way to do this is to click the column A at the first row without real data. Then press SHIFT + CTRL+END from the keyboard. You should now have a large selection of cells. Then click the selection with the second button of the mouse (right click) and select Delete from the pop-up menu. From the Delete window select Entire row. Now save your workbook and file size should have dropped if you had the same problem as I was having.
If the file size is still too big, repeat this procedure to all your sheets. Hope this helps!

Reading through this and several other threads I have a similar issue, or perhaps the same issue much worse: I have a client who has several workbooks (with macros) exhibiting this behavior. The problem we're running into is when we right-click Delete the extra cells... nothing ever happens. The screen flashes, one would assume something has happened. However, hitting ctrl+end still brings me to cell H1048576. I've come across a couple posts that state you must immediately "Save" the file after performing this; makes no difference. File size is still 2.5MB whereas the "un-affected" version is 30kb.

Apparently, my client experiences this on a very regular basis while creating/working with workbooks using Macros, but very little other data. Unfortunately, because of the intricacies of the macros: "Copy/paste to a new workbook and start over" has become an unacceptable answer. My next step is to pickup the phone and call M$; but thought I would try here just in case someone has another idea, or also is unable to "fix" with the solutions above.

Any help is greatly appreciated,
Thanks
scottdrenn