Why would deleting a completely empty column ever INCREASE a file size (by 15%!?)

[d0rian]

I've got a file that's sluggish/laggy and trying to pinpoint the cause, and noticed something very odd. Column CM in my sheet is completely empty (e.g. Ctrl-up takes me to CM1, and Ctrl-down takes me to the bottom cell in the column, so no data whatsoever in it). But when I delete the column and save the file, I see it grow in size from 1700kb to 2050kb. Another oddity: when I then use Ctrl-Z to undo the column-deletion and then re-save, the file size stays at 2050kb(!?). That makes zero sense to me...if I'm simply undoing the column-delete, shouldn't the file ostensibly go back to exactly the same state it had been in? Yet this phantom 300kb in size doesn't go away.

This might not have anything to do with my performance lag, but I find it very odd. Yes, there are several dozen columns to the right and left of column CM, a lot of formulas, named ranges, etc...but none of that seems to explain what's going on. **Note that there's nothing magical about column CM...there are a handful of other empty columns in my sheet, and deleting any one of them produces the same weird file-size-growing effect. Not only that, but just ADDING an empty column in the sheet produces exactly the same thing(!?)

TLDR: Adding an empty column, or deleting an existing empty column, and then re-saving my file causes it to increase 15% in size. Undoing the change and then re-saving doesn't revert the file-size to what it was before...it stays at the weird larger size. Trying to figure out why.

 

[lhartono]

Yes, it is strange.

For Office 2007 and later.

Any conditional formatting on that Column ?
Maybe some fuzzy compression algorithm involved and produce different unique compression ratio for each file. Dont know.
Try to save your file as Excel Binary Format (.xlsb) , produce the same effect?
From my experience, formula and formatting causing most of file size growing, most data size is fixed.
Have you try import or copy content only to blank workbook?

For example, right now my company's sql server generate this formatted xls file daily... mostly 8000 rows, 47 columns. beyond that no data.
If I open it in 2007 it is lagging. so I save as to 2007 excel binary, close and reopen the file, it doesnt help.

Finally after some trial and error, I found a way.
Clear all formats, apply new formatting using macro and delete all empty columns and all empty rows(not clear content) then save.
The file size also reduced by 70%.
After reopen the lag simply gone.

Maybe, just maybe, we share same unknown problems or bug.

 

[MARK858]

Check in Name manager that you had no names that were referring to info in the range that you deleted.

 

[NdNoviceHlp]

Trial zipping and unzipping your file. Dave

 

[d0rian]

check in name manager that you had no names that were referring to info in the range that you deleted.

yes, it is strange.

For office 2007 and later.

Any conditional formatting on that column ?
Maybe some fuzzy compression algorithm involved and produce different unique compression ratio for each file. Dont know.
Try to save your file as excel binary format (.xlsb) , produce the same effect?
From my experience, formula and formatting causing most of file size growing, most data size is fixed.
Have you try import or copy content only to blank workbook?

For example, right now my company's sql server generate this formatted xls file daily... Mostly 8000 rows, 47 columns. Beyond that no data.
If i open it in 2007 it is lagging. So i save as to 2007 excel binary, close and reopen the file, it doesnt help.

Finally after some trial and error, i found a way.
Clear all formats, apply new formatting using macro and delete all empty columns and all empty rows(not clear content) then save.
The file size also reduced by 70%.
After reopen the lag simply gone.

Maybe, just maybe, we share same unknown problems or bug.

FWIW:

• Yes, i thought about defined names / conditional formatting (i have a lot of both), but in troubleshooting before I posted, I deleted all formatting, conditional rules, and defined names, but it didn't help.
• I discovered something that I suppose is 'good news', though equally baffling:
  • So as I wrote, when I deleted (or added) an empty column and re-saved, as I said, the file-size jumped from ~1700kb to ~2050kb.
  • However, when I then closed the file (at which pt the size was still 2050 which I verified in the folder) and then RE-opened it and immediately saved the file again, the size went back down to ~1,700. So...it's almost as if adding/deleting the empty column somehow created 350kb of free-radical 'bloat'...but it was more of a temporary/phantom bloat that was identified / discarded when the file was re-opened (and re-saved).

TLDR: Guess it's good news that whatever caused the weird bloat was just temporary in nature, and likely unrelated to the performance lag I'm trying to fix. Still an odd phenomenon and not sure what Excel process would cause it. Thx for replies.

 

[MARK858]

Saving (or even just closing) and reopening the file usually resets the used range.