XLSB format caused my file size to increase

[jworkman7]

When I have a particularly massive project on a shared drive which needs to run faster and more efficiently on lower quality machines at my company's various branches, I usually save the file in XLSB format to share, because this reduces the the file size, so it can download or be sent via email more efficiently. However today, I had a project and saved it as XLSB, but the file instead increased in size by 1 MB. Not a huge deal, but in the past this usually cuts file size down by about 50% and decreases formula processing times substantially.

Any idea what might be causing this?

 

[01-7700]

conditional formatting

 

[jworkman7]

conditional formatting

I don't have any. I've scoured the workbook to see if formatting or formulas of any kind might be carrying down to the last possible row, but haven't found anything.

 

[01-7700]

lots of index/match?

 

[Rhodie72]

When I have a particularly massive project on a shared drive which needs to run faster and more efficiently on lower quality machines at my company's various branches, I usually save the file in XLSB format to share, because this reduces the the file size, so it can download or be sent via email more efficiently. However today, I had a project and saved it as XLSB, but the file instead increased in size by 1 MB. Not a huge deal, but in the past this usually cuts file size down by about 50% and decreases formula processing times substantially.

Any idea what might be causing this?

Additional userforms. Each userform used to be stored as a bitmap image in the file. If you can use dynamic code to manufacture forms after compilation time you may cut the size down substantially. It's usually a good idea to keep your operational workbooks separate from the data workbook because VBA code can increase file size dramatically.

 

[jworkman7]

lots of index/match?

A few hundred lines of index, but no match

 

[jworkman7]

Additional userforms. Each userform used to be stored as a bitmap image in the file. If you can use dynamic code to manufacture forms after compilation time you may cut the size down substantially. It's usually a good idea to keep your operational workbooks separate from the data workbook because VBA code can increase file size dramatically.

Currently no VBA, macros, or Userforms present in the workbook. It was simply a change from XLSX to XLSB, with nothing fancy in between

 

[xenou]

I don't think there is any reason an xlsb file would have to be smaller, so if it isn't then that's just the breaks. You shouldn't have any problems with xlsx - the only thing I have heard is that load times may be faster with xlsb, but otherwise performance is not effected. If you are going for a faster load time (opening workbook), then try xlsb. If you just want to decrease file size then stick with xlsx. If you just want performance (working with an already open workbook) then either is fine.

 

[jworkman7]

I don't think there is any reason an xlsb file would have to be smaller, so if it isn't then that's just the breaks. You shouldn't have any problems with xlsx - the only thing I have heard is that load times may be faster with xlsb, but otherwise performance is not effected. If you are going for a faster load time (opening workbook), then try xlsb. If you just want to decrease file size then stick with xlsx. If you just want performance (working with an already open workbook) then either is fine.

Yeah, it's not a huge deal. Over time though (I keep this workbook going all year) the data adds up and will be close to 15 MB

 

[Rhodie72]

Just remember that the file is XML based so that's why it's so much smaller than the Excel 97 formats were. It also means that the file is higly compressible which might be worth remembering for storage. NTFS has native file compression available but not many people use it.