Excel Binary Workbook (XLSB vs XLSX)

- Written by Puneet

excelchamps-free-courses-puneet-gogia

Let’s say you are working on a huge sales report with thousands of rows of data, lots of calculations, and several complex charts.

Every time you open or save the file, it takes forever.

Plus, you use macros to automate tasks, like updating the report with new weekly data. In this case, switching to an Excel Binary Workbook (.xlsb) can help. It will make your file smaller and faster to open and save.

excel-binary-workbook

In this tutorial, we will look at all the aspects of using Binary Workbook in Excel

Quick Notes

  • Binary Workbooks are lighter in size.
  • These files can handle macros.
  • Binary format open and save faster.
  • It has less compatibility with other spreadsheet programs.
  • If a file gets corrupted, it’s difficult to recover data.

What is Binary Format (XLSB)?

An Excel Binary Format Workbook (File) saves data in a binary format, unlike XML, which other formats (like XLSX) use. The user interface of a binary file is the same as that of other files. You can use it in the same way you use other workbooks.

The best way to understand it in simple language is to think of it as packing a suitcase.

In the binary format, Excel packs all your data into a tight, efficient package, making the file smaller and faster to handle. But you can add and delete the worksheets, create charts and pivot tables, write formulas, and write codes.

Advantages of a Binary Workbook

Here are the advantages of using a Binary Workbook in Excel to store data.

  1. Smaller File Size: Binary workbooks are more compact, saving space on your computer and making them easier to share.
  2. Macro Support: Binary workbooks can run macros, so you can automate repetitive tasks and save time.
  3. Handles Big Data: These files handle large amounts of data better, reducing the chance of your workbook slowing down.
  4. Better for Complex Workbooks: XLSB can handle complex workbooks with many formulas and data more smoothly.
  5. Less Likely to Break: These files are robust and less prone to breaking, so you can rely on them for important projects.
  6. Faster Performance: They open and save faster, so you spend less time waiting for a large workbook to load.

Save, Create, and Open a Binary Workbook in Excel (XLSB)

To save a workbook in a binary format, first of all open the workbook that you want to save as a XLSB.

excel-binary-workbook
  1. Click on the “File” tab in the ribbon to open the backstage view and then select ‘Save As’ from the menu.
  2. In the save as dialog box, you can choose the location on your computer to save the file. In the ‘Save as type dropdown menu, select “Excel Binary Workbook (*.xlsb)”.
  3. Enter a name for your file in the ‘File name’ field, and then click the ‘Save’ button

Or, you can also use the keyboard shortcut to open the save as dialog box, (Alt → F → A)from the “Save as type” drop-down, you can select the “Excel Binary Workbook.” Then, you can save it the way you normally save a workbook.

Not just saving an Excel workbook as a binary workbook, you can create a new binary file in Excel at the first place.

For this, you just need to follow the same steps that you have learned above. Open the dialog box and select the .XLSB format to save the file.

XLSX Vs. XLSB

As I said, one of the biggest differences between XLSX and XLSB is that they save the data in the workbook in binary format.

In the below screenshot, we have two workbooks with the same set of data (253000 cell entries). The XLSX format has a size of 2427 KBs, and the binary format has a size of 1784 KBs.

save-workbook-in-binary-format

Below is table that shows you some of major differences between XLSX & XLSB.

Feature
XLSX
XLSB
File Extension
.xlsx
.xlsb
File Format
Open XML Format
Binary Format
File Size
Larger
Smaller
Speed
Slower to open/save
Faster to open/save
Macro Support
Supports macros with .xlsm extension
Supports macros
Data Storage
Text-based XML
Binary
Compatibility
Widely compatible with other programs
Less compatible with non-Excel programs
Corruption Recovery
Easier to recover due to text format
Harder to recover due to binary format
Security
Generally considered more secure
Potentially less secure due to binary nature
Usage
Common for sharing and archiving
Common for large and complex workbooks

Then, Should I Use XLSB?

XLSX is suitable for basic use and provides all the standard features you need, making it perfect for workbooks that aren’t very large or complex.

Additionally, if security is a priority, XLSX files are easier to inspect, making them safer for sharing sensitive information.

For workbooks with extensive formulas, charts, and data, XLSB can manage complexity better without performance issues. When frequent edits and saves are required, XLSB’s works faster than other formats.

If minimizing file size for storage or sharing is important, XLSB compresses data more effectively, as we have learned above. It can help you hold a large data set, especially when you don’t need to work on the file.

The best combination of binary files is with the POWER QUERY. You can directly extract data from multiple binary (XLSB) files with the help of a power query.

Merge Multiple Excel Files (Combine Data)

Take Care of Security…

Like the XLSM file format, which is the specific Excel file format for storing or rummaging macros, XLSB can also store macros.

This increases the security concern if you get an XLSB file from other users which contains a macro.

Normally, with XLSM, you know it can have macros; you can disable macros in the Excel application before opening it.

That is not the case with XLSB. If it contains a macro and you have macros enabled. It can run a macro automatically.

Disadvantages

  1. You can’t easily open XLSB files in non-Excel programs like Google Sheets or LibreOffice, as they may not support this format well. Sharing files with others who don’t use Excel could be problematic.
  2. Data recovery is challenging with a Binary Workbook (.xlsb) because the data is stored in a binary format, which is not human-readable.
  3. Some third-party Excel add-ins or extensions might not fully support the XLSB.

Get the Excel File

Last Updated: May 30, 2024