XLSB file size

[pi]

Hi!

Using Excel 2007. I work with XLSB to optimize file sizes.

I have a huge data table (file size 16 MB). I put it into a file with some analysis (some pivot tables without saving data, some formula driven tables). File size went up by less than 1 MB. Great.

Then I did some formula changes, added a sheet with only 12 cells used (!). File size went to 34 MB. I closed the file, reopened, saved again, down to 23 MB. But there it is. Still some 6 MB too much.

What's wrong? Any way to force Excel to save as little as possible?

pi

 

[GlennUK]

You say that you did some formula changes. Did you do that in addition to the extra sheet with the 12 used cells?

Copy the file ... in the copied version, remove the new sheet, and save. What size is that file afterwards?

 

[pi]

Thanks for reply! Actually the size increase was already happening before I added the one sheet.

File is now at 30 MB (I have actually added more sheets in the meantime). The 12 cells sheet has grown a by now. Anyway, after deleting the file size is less than 100 k smaller.

If I delete all sheets except for the original data sheet, I get 28 MB. No hidden sheets. Last cell is actually used.

pi

 

[GlennUK]

Just to make sure .... this isn't a Shared workbook is it?

 

[pi]

Right, just a normal thing. Not even data connections.

 

[GlennUK]

28 MB instead of 16 MB ... now I'm as stumped as you. That's a massive file size difference. And you've checked the last cell position, so it can't be that.

How about defined names ... can you check all defined names to see if anything odd has appeared there?

 

[pi]

No names whatsoever.

pi

 

[Johnny C]

Have you tried resetting the last used cells in each sheet?

Do control-end, if it goes beyond the bottom-right cell :-
delete all rows/columns beyond the bottom right cell with data in to the end of the worksheet
click on cell A1
save

That forces a recalc of filesize.

Have you a lot of formatting? Formatting can really mess up the size of a spreadsheet, especially conditional formats if they refer to particular cells. I once reduced a 34mb w/b to 1.6Mb by just stripping the conditional formats.

 

[pi]

I've done the last cell thing. No success.

Yes, there is formatting, but it is consistent throughout each column. No conditional formatting.

 

[GlennUK]

Did you do formatting for entire columns? Or only down as far as the used range goes?