Excel holding on to blank cells all the way to the limits of the worksheet?

[bigequator]

I recently started a job within an HR department where spreadsheets are run through the ringer (e-mailed, opened and saved between different Excel versions, posted to Sharepoint, etc. ad nauseum). Being the new guy that knows more about spreadsheets than most the staff, I'm often given workbooks with the direction "Can you do anything about this?" (I'm sure you all know the situation well). I'm using Excel 2010 32-bit.

A couple of these cases involve spreadsheets that are abnormally large and I find out that the Used Range is all the way to bottom (1M Rows) or all the way to the right (16K Cols) with blank cells. There may be cell colors or borders applied but they are empty (no content (incl. spaces), no formulas, no special characters that I can find, etc.). If Excel behaves properly, I'm able to delete the rows or columns and get the spreadsheet back into working order. It is curious to me though that 1) Excel doesn't know to reclaim the space and 2) the size of the file after clean up may be a fraction of its former size. Both seem to indicate that there was something in the cells somewhere (and a lot of it!) but I cannot find it (and I'm good with Excel!). The user claims no knowledge of having gone to the extreme ends and accidentally putting data there.

So, I'm looking to help these folks stop this from happening in the first place (what causes it? worksheet corruption?) and to understand it for myself. I have tried to re-create the problem without success.

Also, any tools to help with a very large workbook suffering from this without having to open it in Excel (it is almost 50MB in size and gives Excel heartburn when trying to clean up the "corrupted" sheet). Thanks!! Jim.

 

[JoeMo]

If the user needs to fill a portion of a row or column with a background color, as an example, sometimes they simply fill the entire row(s) or column(s). Similarly, for adding borders and the like. This cause file bloat.

I'm not aware of anything to clean-up the mess w/o opening the workbook first.

 

[bigequator]

Thanks for the reply, Joe! I tried doing that intentionally and Excel didn't flinch - it still showed the Used Range at the end of my data. I even tried putting in a value at XFD10, then coloring and bordering the rows 1-9. It showed the used range out to XFD10 but when I deleted the value, it snapped right back (as it should). So the question remains, what are the users doing that causes the bloat to persist? This could be a worksheet corruption - like I said, these workbooks are in extreme conditions, between the users and the environments. Thanks!

 

[JoeMo]

Thanks for the reply, Joe! I tried doing that intentionally and Excel didn't flinch - it still showed the Used Range at the end of my data. I even tried putting in a value at XFD10, then coloring and bordering the rows 1-9. It showed the used range out to XFD10 but when I deleted the value, it snapped right back (as it should). So the question remains, what are the users doing that causes the bloat to persist? This could be a worksheet corruption - like I said, these workbooks are in extreme conditions, between the users and the environments. Thanks!

The issue is not that Excel doesn't know where the used range is (although there are times when it seems not to), but rather that the coloring and bordering takes memory thus leading to file bloat. Try opening a new workbook, enter a value in A1, save the workbook and note the file size. Re-open it and fill and border all the cells (click on the rectangle above the intersection of row 1 and column A to select all cells on the sheet). Save and note the file size.

 

[bigequator]

OK, I see now that my tests were invalid. However, I do think there is still something going on as I'm hard pressed to believe that just color and/or just border can take a file from 15MB to 25MB. But maybe there is more memory eating that I imagine. Thanks, Joe, I appreciate the feedback! Question resolved!

Jim.