XLSB file size

pi

Board Regular
Joined
Apr 11, 2006
Messages
207
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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?
 
Upvote 0
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
 
Upvote 0
Just to make sure .... this isn't a Shared workbook is it?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
I've done the last cell thing. No success.

Yes, there is formatting, but it is consistent throughout each column. No conditional formatting.
 
Upvote 0
Did you do formatting for entire columns? Or only down as far as the used range goes?
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top