There are over 15+ types of excel formats, Today in my blog I will take you through three most of the used excel formats i.e XLSX, XLSB & XLSM. With that I will be sharing some of their importance, advantage, disadvantage, Myth.
XLSX vs XLSB vs XLSM
File extension is basically group of letters which appears after a full stop “.” For example (abc.xlsx) in this example “xlsx” File extensions are 2 to 4 characters long and are related to the file's format. With this let’s learn more about the different types of file extensions that Excel uses and see how are they different from one another.
Above are icons you see, which differentiate each format
1) XLSX format
XLSX file extension is default file format which is widely across the globe. Mostly in every organization, industry, institutions uses this format. It's a ZIP-compressed, XML-based spread sheet file created by Microsoft Excel version 2007 and later.
2) XLSM File Format
XLSM file is a macro-enabled spread sheet created by Microsoft Excel, a widely used spread sheet program that comes with every edition of the Microsoft Office suite. It was mainly develop to run all the Macro enabled file which cannot run in normal file formats.
3) XLSB File Format
XLSB file extension is basically a Binary Workbook file. (In layman’s language Binary a numeric system that only uses two digits 0 & 1. Computers operate in binary, meaning they store data and perform calculations using only zeros and ones) Hence this file format store information in binary format instead of XML like with most other Excel files does. Since XLSB files are binary, they can be read from and written too much faster, making them extremely useful for very large spread sheets.
As we went through the advantages & Disadvantages of different formats lets learn more about Binary XLSB Format as it has most of the advantage
Excel files with lots of formulas, data and objects (e.g. shapes) can expand considerably in size. Large files take longer to open/save and occupy valuable space on discs or in mailboxes when attached to emails. Binary file compression offers potential cost savings, because it reduces the disk space required to store files and decreases the bandwidth needed to transport files through e-mail or networks.
The XLSB Format Explained
For the technically minded, the XLSB file format, code named BIFF12, is a ZIP container based on the Open XML file specification. The key difference between XLSB and XLSX - XLSM is that file parts within the zipped package are compressed binary components (.bin) encoded in a proprietary format, instead of being readable XML code. Binary files are optimized for performance and can store anything you can create in Excel.
Facts & Myths About Binary Excel Workbooks
Smaller binary files open and save much faster than Excel workbooks (.xlsx or .xlsm)
Ø Binary files have the same RAM memory requirements as other Excel file formats, as compression decreases the space that the file occupies on the hard disk only.
Ø Excel Binary & macro-enabled workbooks may store VBA/macro code, a potential security concern. Macros cannot be stored in the default Excel .xlsx format.
Ø Excel Binary files are encoded in a proprietary compressed file format and not in the open, standards-based XML file format (OpenXML).
Ø Myth: "I am not sure what is lost, but I know binary workbooks are more compact..." No data is lost, when saving a workbook as a binary file format. Binary files use a better compression than the standard ZIP compression used in other Open XML file formats.
Ø Myth: "You can then go from xlsb back to xlsx and the smaller size prevails..." Wrong. Saving a binary file as XLSX or XLSM will result in a larger file size - ceteris paribus - (with all Excel features stored in the file being the same)
Ø Myth: "My workbook doesn't have macros, so I cannot SaveAs in binary format" Wrong. You can save any workbook (with or without macros) in binary format.
Ø Myth: "Binary workbooks are more prone to file corruption" Over years, we have come across about an equal number of binary and non-binary corrupted workbooks. Therefore, the above claim seems to be a myth. Workbook format is not a contributing factor of file corruption.
Ø Myth: "Formulas, VLOOKUP in particular, recalculate much faster in binary files" Excel data structures and algorithms residing in memory (RAM), including calculation, are not affected in any way by the file structure used, apart from Open and Save operations obviously.
With every Advantages comes a Disadvantages some of the Disadvantages for Binary Files are shared below
· Binary files cannot not be accessed by programs that understand XSLX, XLSM file format only. We have come across several file previewing software, which cannot display Excel binary files. You need to save binary workbook in XLSX or XLSM file format.
· However, applications that read Excel VBA directly, can access binary files without an issue, as VBA code is not stored in XML format! VBA code is not compressed in binary xlsb files.
· Power Query cannot read data from binary XLSB workbooks. Excel shows an odd error after a long delay, if you try to create a Power Query from a binary workbook.
· There is no way to Test If A Binary File Has Macros Without Opening It also there is no macro-free binary file format.
Conclusions
On a daily basis I would recommend using to XLSX and XSLM as standard Excel file formats. However It is worth reaching out to the XLSB file format whenever you file starts running slow or uses an enormous amount of space.
XLSB will actually not benefit small Excel files and you might even see small XLSB files taking more space than small XLSX/XSLM files. Your clients / coworkers may also have doubts when opening XLSB files as Excel treats these file formats with an extra dose of caution.
Use XLSB file format MAINLY with very LARGE Excel files.
Very useful information!