Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • December 03, 2016, 08:00:19 PM
  • Proudly celebrating 10 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Excel 2007 workbook bloat - potential fix?  (Read 4299 times)

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,605
    • View Profile
    • Donate to Member
Excel 2007 workbook bloat - potential fix?
« on: April 07, 2011, 07:47:37 PM »
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

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,184
    • View Profile
    • Donate to Member
Re: Excel 2007 workbook bloat - potential fix?
« Reply #1 on: April 08, 2011, 03:18:24 AM »
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,220
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Excel 2007 workbook bloat - potential fix?
« Reply #2 on: April 08, 2011, 03:31:42 AM »
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?
Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,605
    • View Profile
    • Donate to Member
Re: Excel 2007 workbook bloat - potential fix?
« Reply #3 on: April 09, 2011, 04:19:31 AM »
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.

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?

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)