Excel 2007 workbook bloat - potential fix?

[Target]

It appears that inexplicable bloat in Excel workbooks is a reasonably common issue, so my experiences with this phenomenon may be useful to others

A shared workbook had blown out in size considerably for no reason that I could see (ie from 4M to 33M)

I had already gone through and removed as much unnecessary formatting as I could (it's a shared resource) but it made no difference

Googling didn't turn up anything particularly useful (the main fix seems to be removing unnecessary formatting, which I'd already done), but it did remind me that office 2007 files are zipped xml bundles.

When I opened the (zip) file, I found 2 worksheets were considerably larger than the others (one was 180M and the other was 89M).  AHA! says I

I went back into Excel and MOVED the sheets out, saved, then checked the size, and...no change

I went back into the bundle and found that the bloat had moved to the next sheets (WTF?), so instead of sheet26 being 180M, now sheet25 was 180M.

I made a fresh copy and this time I COPIED the offending sheets out, then went back into the bundle and DELETED the offenders, saved, then checked the size.

This time the file had come back to a more reasonable 4M, so I re-opened it with the intention of copying the deleted sheets back in.

On opening the file Excel threw up a 'this file is corrupted, do you want to repair' message to which I answered yes. After letting it do it's stuff I saved and went to copy the deleted sheets in only to discover that Excel had automagically reinstated them!!

I've done as much testing as I can (the workbook contains data only, no additional functionality) and as far as I can see no data was lost to the exercise, and the aforementioned bloat is now gone (woohoo...)

I can't vouchsafe this method, however it seems to have worked in this instance.  YMMV, so if you're going to use it I would suggest you make a copy of your file(s)  first, then confirm the results yourself before you replace your originals.
   

[fenixproductions]

2Target
I had similar issue recently and it appeared that there were few hundred thousands of empty cells added to my XLSX file. Since each one requires proper tags in XML file whole document size was enormous.

I've simply deleted additional rows/cols and it helped. No need for any further magic.

[Renegade]

Thanks for posting that.

I've had other Office documents passed to me that were horribly slow to work with. They weren't particularly large (large, but not massive), but just sssssllllloooooowwwww... Did you have any of that in the bloated excel file?

[Target]

2Target
I had similar issue recently and it appeared that there were few hundred thousands of empty cells added to my XLSX file. Since each one requires proper tags in XML file whole document size was enormous.

I've simply deleted additional rows/cols and it helped. No need for any further magic.

-fenixproductions (April 08, 2011, 03:18 AM)

This is a common issue - it usually occurs when 'someone' enters something in a cell way outside of the used range, though adding formatting to unused cells/rows/columns will do it as well.  The supposed fix is to highlight and delete ALL the unused columns and/or rows and delete them (thus removing the any/all unnecessary content/formatting)

Thanks for posting that.

I've had other Office documents passed to me that were horribly slow to work with. They weren't particularly large (large, but not massive), but just sssssllllloooooowwwww... Did you have any of that in the bloated excel file?

-Renegade (April 08, 2011, 03:31 AM)

no, it was purely data (though there was a bit of formatting that I think is unnecessary, but not in the sheets that were affected).  Watch out for lots of calculations as these can (and will) slow things down enormously (calculations done in VBA are several orders of magnitude worse) - if you think this might be the culprit try turning off the auto calculate function.  There is one bug that pops up a bit in Google searches that indicates something wierd with cell borders.  According to the various post simply removing the offending formatting doesn't work but copying and pasting the content (only) to a new sheet seems to do the trick (YMMV of course)