Advantages Of Excel's Binary XLSB File Format
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. Learn how to reduce the file size of Excel files, so that your workbooks load and save much faster.
Microsoft Office Excel 2007+ files can be 'Saved As' in several other formats besides the default XLSX format. The file formats that are available in the Save As dialog box may vary, depending on what type of sheet is active (e.g. a worksheet, chart sheet).
Microsoft Office Excel 2007+ files can be 'Saved As' in several other formats besides the default XLSX format. The file formats that are available in the Save As dialog box may vary, depending on what type of sheet is active (e.g. a worksheet, chart sheet).
Save As Binary in Excel 2007
An Excel file saved as a Binary Workbook (XLSB extension) could be significantly smaller than the one saved as an Excel Workbook (.xlsx or .xlsm extensions).
Save As Binary in Excel 2010 or later
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 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.
Only Excel workbooks smaller than 5MB stored in a OneDrive folder will open in an Excel Web App.
- 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.
- Save as Binary is available in Excel 2003, if the Excel 2007 compatibility pack has been installed along with SP3.
- 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.
You can protect macros in binary workbooks with Unviewable+ VBA. - 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. Read about Excel file corruption here - Myth: "Customized ribbon UIs are not available in XLSB files"
Wrong. Our 2048 game in Excel has a custom ribbon UI and is saved in a binary format. - Myth: "Binary workbooks cannot be opened in LibreOffice or OpenOffice"
False! Both suites support the XLSB file format for compatibility with Excel. - 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.
Disadvantages Of Binary Files
Binary files cannot not be accessed by programs that understand the 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. Use the latter if macros are present in the file.
|
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. If you want to shrink your VBA code, use our VBA Code Cleaner powered by Ribbon Commander.
|
Power Query cannot read data from binary XLSB workbooks. Excel shows an odd error after a long delay, if you try to create a Powery Query from a binary workbook.
|
|
Test If A Binary File Has Macros Without Opening It
There is no macro-free binary file format. To fiind out if a XLSB file has a VBA project before opening it, use our Macro Mover add-in bundled for free with the Ribbon Commander framework. The Macro Mover add-in can detect macros in closed workbooks.
The Excel Personal Workbook
The personal macro file, if present, is opened as a hidden workbook every time you start Excel. The personal workbook is saved by default in binary format (Personal.xlsb) in order speed-up opening.
How To Compress VBA In Binary XLSB Files
Unfortunately, VBA code doesn't properly clean-up after itself, so lots of junk gets left behind in performance caches during editing of VBA projects. Not only does this increase the file size, but it may lead to odd behaviour of your program at runtime or slow loading.
The Ribbon Commander VBA Cleaner removes these redundant caches from Office macro-enabled files. Read more here.
The Ribbon Commander VBA Cleaner removes these redundant caches from Office macro-enabled files. Read more here.
