You are in: how-to > Binary Files in Excel

BINARY FILES IN EXCEL (XLSB)


01/09/18 |Excel 2013/2016 | All levels | by Jorge Vilar

Working with large Excel files is often a PROBLEM. They open slower, they take an eternity to save and they often need to be uploaded to the Cloud to be shared with your co-workers. Why not explore the benefits of the XSLB file format then?

WHAT IS A XLSB FILE FORMAT?

XLSX and XLSM files are in fact compressed archives with XML files inside. That is because Microsoft has opened the Excel file format and decided to break the insides into XML files. When an XLSX or XLSM file is saved Excel needs to break it down into separate XML files, compress it and finally save it as XLSX or XLSM.

The XLSB file format on the other hand is a binary Excel file. It resembles the old XLS file format which was also a binary file. What happens when Excel wants to save an XSLB file? Not much really. It mostly dumps the binary data into a binary file and saves it as XLSB.

HOW TO SAVE A XLSX IN XLSB

Press File > Save As. Choose the location.
When Save As dialog box appears, in Save as type choose Excel Binary Workbook (*.xlsb):

Press Save.

XLSB advantages (XLSB vs. XLSX)

Smaller file size – the Excel binary file uses noticeably less space. This is more noticeable especially when working with large Excel files. In some cases, the XLSB file requires 10% of the original file size – this is more visible with VERY large files

Opens/saves more quickly – loading binary data is faster than parsing text (XML) files – similarly as you would compare opening a book in Spanish and having to translate every sentence to English as opposed to picking up a ready translated copy. Similarly, when saving the file – the binary format is more efficient than dumping the data back into the XML and then compressing it. From my experience XLSB files open and get saved 2x faster

formulas above the 8192 character limit. In other file formats they don’t save properly.

It seems there is nothing less obvious to do then to start working only on the XLSB file format. However, it’s not a straightforward decision as there are some minor setbacks.

XLSB disadvantages (XLSX vs. XLSB)

Compatibility – the XLSB Excel format is not supported by Excel 2003 and earlier versions, which is less of a problem nowadays.

Security (VBA) – with the distinction between the XLSM and XLSX format you know which files may or not contain VBA macros. With XLSB you won’t know for sure. So, beware when opening XLSB files from unknown sources or from people/websites you don’t trust.

You can’t make changes to the Excel Ribbon when working on an XLSB. You must temporarily save your file as XLSX or XLSM, makes changes and save back as XLSB.

Lack of interoperability with third-party tools. XLSB is a binary file format unlike the open XML XLSX and XLSM files. Hence you often won’t see your XLSB files working everywhere – like in OpenOffice.

Common myths about XLSB

Do XLSB runs formulas faster than XLSX?
NOT TRUE - XLSB file are only loaded and unloaded faster (saved and closed) than XLSX files. Afterwards both formats run in RAM memory with similar performance on the same Excel engine. Hence, you won’t see your Excel formula’s running significantly faster. However, if you do save your file frequently you will definitely notice that the XLSB file format saves faster.

Do XLSB files crash more often?
NOT TRUE - XLSB file are only loaded and unloaded faster (saved and closed) than XLSX files. The XLSB file format does not in any way increase the probability of crashing. However, if the file does crash it may be harder to recover. XLSB are binary files, where XLSX and XLSM files are in fact compressed XML files – text files in XML format. Therefore, in a critical situation you have definitely a better chance of reading a text file than a binary file. Then again, I wouldn’t worry about this too much.

CONCLUSIONS

On a daily basis I would recommend sticking to XLSX and XSLM as standard Excel file formats. 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 co-workers may also have doubts when opening XLSB files as Excel treats these file formats with an extra dose of caution.

Use the XLSB file format MAINLY with very LARGE Excel files.

- end -

Comments, suggestions and questions are always welcome - jorge@jorgevilar.com.

 •   top...»