INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel: xlsm versus xlsb ?

Excel: xlsm versus xlsb ?

(OP)
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.)

RE: Excel: xlsm versus xlsb ?

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: //www.engineering.com/AskForum/aff/32.aspx
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers

RE: Excel: xlsm versus xlsb ?

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/

RE: Excel: xlsm versus xlsb ?

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: //www.engineering.com/AskForum/aff/32.aspx
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers

RE: Excel: xlsm versus xlsb ?

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/

RE: Excel: xlsm versus xlsb ?

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

RE: Excel: xlsm versus xlsb ?

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/

RE: Excel: xlsm versus xlsb ?

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

RE: Excel: xlsm versus xlsb ?

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:
  • you need compatibility with 2003 or earlier (in which case use xls)
  • you have a definite need for xlsm
  • you need the spreadsheet to be guaranteed macro-free, in which case use xlsx

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Excel: xlsm versus xlsb ?

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

RE: Excel: xlsm versus xlsb ?


Quote:

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/

RE: Excel: xlsm versus xlsb ?

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close