Excel: xlsm versus xlsb ? 2

[TomBarsh]

Every so often I stumble across Excel issues that I investigated years before (this occurs in my work life too) and have forgotten.

So I just realized that Excel 2007 offers the xlsm and the xlsb formats. My copy of Walkenbach's book for 2007 barely even mentions the xlsb format. But he does discuss that earlier versions of Excel used the binary format; presumably, the xlsb is pretty much an extension of the earlier type.

I have some files that are packed with goodies. In xlsm format they are around 5-6 MB. In xlsb they are a bit more than half of that.

These files are not used by anyone other than myself, and are used only with Excel. There are no Ribbon modifications in them.

Should I have any concerns about using the xlsb format so that the files are smaller and load/save faster? I have browsed around on the web and gleaned what's out there for information. There hasn't been much discussion at all here in the Eng-Tips forum.

(Funny thing is, I have some binary copies of a couple of these files and I remember using them several years ago. For the life of me, I don't remember or know why I stopped using that format, or if I was even aware that I stopped at the time.)

 

[IRstuff]

According to what I've read .XLSB format is actually an OpenOffice creation from 2008, that Microsoft decided to adopt and support. There's a description of the format here:

http://www.openoffice.org/sc/excelfileformat.pdf

Not sure what it has, or doesn't have, but I would assume that Microsoft will be more than happy to complain about unsupported features if you try to save to .XLSB format with something that the format doesn't support. Note that Tek-Tips.com has a Microsoft Office forum, wherein .XLSB format is at least mentioned in passing:

http://www.tek-tips.com/viewthread.cfm?qid=1585535

TTFN
I can do absolutely anything. I'm an expert!
homework forum: //

http://www.engineering.com/AskForum/aff/32.aspx

faq731-376 forum1529

 

[IDS]

IRStuff - xlsb was introduced in 2007 so it can't have been an Open Office format.

I always use xlsb (or xlsx if there are no macros) and I have never had any problems with it. I don't know why everybody else uses xlsm. I don't see any advantages with it, unless you are going to access information from the file from outside Excel.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

The version that Microsoft adopted is supposedly from 2008, which is the last mod date from the cited specification. The specification says that OpenOffice had been working on the specification since 2001

It's definitely way more dense than Excel 97-2003 format, but for my stuff, there doesn't appear a major difference between XLSB and XLSX, although XLSB is about 20% denser.

TTFN
I can do absolutely anything. I'm an expert!
homework forum: //

http://www.engineering.com/AskForum/aff/32.aspx

faq731-376 forum1529

 

[IDS]

I'm pretty sure you have it round the wrong way. Xlsm is the Microsoft version of the open Xlm format, and Xlsb is a proprietary binary format. The only downside I can see with xlsb is that you can't open it with other software, but if you have macros other software can't run them anyway.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

That could be; I don't think I've ever even run across that format, until now.

TTFN
I can do absolutely anything. I'm an expert!
homework forum: //

http://www.engineering.com/AskForum/aff/32.aspx

faq731-376 forum1529

 

[bacon4life]

One drawback of xlsb over xlsx is that windows search does not look inside xlsb files. Not sure if it looks inside xlsm files.

 

[IDS]

Bacon4life - see attached Control Panel settings, under System and Security, Indexing Options, Advanced Options.

Both xlsb and xlsm are selected. I don't know if this is the default, but it's easy to change, and it does work, I just checked it.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

By the way, the Windows search function now works really well from File Explorer. It is fast, and if you have it set up to display previews, these work for both office files and pdfs, and are near instant.

The search "app" on the other hand is near useless.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[dynatune4xl]

xlsm files are more compressed than xlsx files and allow of course macros. xlsb files are more compressed than xlsm files, allow macros and can make a more than 50% difference in file size with respect to xlsx files. Especially when reading large files and worksheet preparation becomes a cpu issue this file format is unbeatable in speed. Needs to be excel 2007 or above.

cheers

dynatune4xl

 

[BigInch]

http://blog.datasafexl.com/excel-articles/advantages-of-xlsb-excel-binary-format/

 

[IDS]

Thanks BigInch, good summary.

The only thing I would contest is that they say "we highly recommend this (xlsb format) for very large and complex files", whereas I recommend it for all files unless:
[ul]
[li]you need compatibility with 2003 or earlier (in which case use xls)[/li]
[li] you have a definite need for xlsm[/li]
[li] you need the spreadsheet to be guaranteed macro-free, in which case use xlsx[/li]
[/ul]

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Denial]

Doug,
Under what circumstances might one have "a definite need for xlsm"?
Thanks.

 

[IDS]

Under what circumstances might one have "a definite need for xlsm"?

I presume (but don't know) that they can be read by Open Office, and there are (I presume) other programs that will read them without needing Excel.

But I always use xlsb.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[BigInch]

It is apparently the data exchange issue. Unless binary is specified with XLB, the more universal XML file format is used. Many programs can read it, but it comes at a heavy overhead cost to include all the extra notation involved.