When should the xlsm or xlsb formats be used

Question

Since Excel 2007  Microsoft has split the classical  xls format to several formats  in particular   xlsx   xlsm   xlsb   I ve got no problem to understand the use and purpose of  xlsx format but I am still wondering whether we should use a  xlsm or a  xlsb format when creating a file containing some VBA   Of course  you can find some topics on the web  for instance    on Microsoft answers forum on Microsoft blog that was pointed in the previous link  yet I ve parsed until the 10th page without finding a ref to  xlsb  this topic from another forum   What I ve understood from this last link is that  xlsm is some kind of XML format and thus  needed for custom ribbon tab   Beyond the conceptual difference between the format   xlsm is based on XML VS  xlsb is a binary file   is there any practical difference when using any of this file  apart from the ribbon customization   Have you ever seen any real difference when using any of these formats

User · Accepted Answer

They re all similar in that they re essentially zip files containing the actual file components   You can see the contents just by replacing the extension with  zip and opening them up   The difference with xlsb seems to be that the components are not XML-based but are in a binary format  supposedly this is beneficial when working with large files     https   blogs msdn microsoft com dmahugh 2006 08 22 new-binary-file-format-for-spreadsheets

User · Answer

xlsx loads 4 times longer than  xlsb and saves 2 times longer and has 1 5 times a bigger file   I tested this on a generated worksheet with 10 000 rows   1 000 columns   10 000 000  10 7  cells of simple chained      1 formulas    --------------------------------                      xlsx      xlsb       -------------- -------- --------      loading time    165s       43s       -------------- -------- --------      saving time     115s       61s       -------------- -------- --------      file size        91 MB     65 MB     --------------------------------     Hardware  Core2Duo 2 3 GHz  4 GB RAM  5 400 rpm SATA II HD  Windows 7  under somewhat heavy load from other processes    Beside this  there should be no differences  More precisely       both formats support exactly the same feature set   cites this blog post from 2006-08-29  So maybe the info that  xlsb does not support Ribbon code is newer than the upper citation  but I figure that forum source of yours is just wrong  When cracking open the binary file  it seems to condensedly mimic the OOXML file structure 1-to-1  Blog article from 2006-08-07

User · Answer

Just for posterity  here s the text from several external sources regarding the Excel file formats  Some of these have been mentioned in other answers to this question but without reproducing the essential content   1  From Doug Mahugh  August 22  2006         the new XLSB binary format  Like Open XML  it   s a   full-fidelity file format that can store anything you can create in   Excel  but the XLSB format is optimized for performance in ways that   aren   t possible with a pure XML format         The XLSB format  also sometimes referred to as BIFF12  as in    binary   file format for Office 12     uses the same Open Packaging Convention   used by the Open XML formats and XPS  So it   s basically a ZIP   container  and you can open it with any ZIP tool to see what   s inside    But instead of  XML parts within the package  you   ll find  BIN parts      This article also refers to documentation about the BIN format  too lengthy to reproduce here   2  From MSDN Archive  August 29  2006 which in turn cites an already-missing blog post regarding the XLSB format      Even though we   ve done a lot of work to make sure that our XML formats   open quickly and efficiently  this binary format is still more   efficient for Excel to open and save  and can lead to some performance   improvements for workbooks that contain a lot of data  or that would   require a lot of XML parsing during the Open process    In fact  we   ve   found that the new binary format is faster than the old XLS format in   many cases    Also  there is no macro-free version of this file format       all XLSB files can contain macros  VBA and XLM    In all other   respects  it is functionally equivalent to the XML file format above       File size     file size of both formats is approximately the same  since   both formats are saved to disk using zip compression Architecture       both formats use the same packaging structure  and both have the same   part-level structures  Feature support     both formats support exactly   the same feature set Runtime performance     once loaded into memory    the file format has no effect on application calculation speed   Converters     both formats will have identical converter support

User · Answer

One could think that xlsb has only advantages over xlsm  The fact that xlsm is XML-based and xlsb is binary is that when workbook corruption occurs  you have better chances to repair a xlsm than a xlsb

User · Answer

The XLSB format is also dedicated to the macros embeded in an hidden workbook file located in excel startup folder  XLSTART    A quick  amp  dirty test with a xlsm or xlsb in XLSTART folder    Measure-Command    x   New-Object -com Excel Application   x Visible    True    x Quit       0 89s with a xlsb  binary  versus 1 3s with the same content in xlsm format  xml in a zip file

[excel] When should the xlsm or xlsb formats be used?

Examples related to excel

Examples related to excel-2007

Examples related to xlsm

Examples related to xlsb