Reduce file size and load/save time with .xlsb format

Written by Peter Albert on . Posted in Starter

Executive Summary

Store any Excel 2007+ files as Excel Binary Workbook (.xlsb), instead of the default Excel Workbook or Excel Macro-Enabled Workbook! This will reduce the size of the file significantly (usually by 30-60%!) and with it the load/save time.

To make this the default format, use OptionsSaveSave file in this format and change the dropdown to “Excel Binary Format“.

Detailed description

By default, Excel 2007+ stores new files as .xlsx (Excel Workbook) – or if it contains macros as .xlsm. Internally, both file formats are actually zip files (try renaming a file to .zip to see the inner content if you’re curious!) – and each worksheet as well as all other content are stored as text files using XML. This is a great feature by itself, as it makes life for developers of external tools much easier to read or write Excel files. However, for the normal user, the fact that everything is stored as XML is not relevant.

What surprisingly few people know: Excel 2007+ has another, native format called Excel Binary Workbook (stored as .xlsb). This format can contain macros (you’ll still get a warning upon opening the file if it contains macros). This format is a proprietary, binary format that fully support all Excel features the same way as .xlsm does. But as everything is stored in the less “bloated” binary format, the overall file size is significantly smaller! Depending on your data structure, my experience is that your file size will be reduced by 30-60% on average! Especially on large models this can make quite a difference, .e.g if the model still fits in an email. And with the reduced file size comes a proportionally reduced load/save time (of course excluding any time required to calculate the model!).

The file format has no disadvantage apart from the reduced compatibility with external programs – and you can always store a file back as .xlsm/x in case you need to use it in another program.

If you want to use the file format by default (and well you should!), best change it in the Excel options: enter image description here

For reference, here’s what Microsoft is saying to the file 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

Tags: ,

Trackback from your site.

Peter Albert

Worked 5 years as Consultant and Project Manager at McKinsey & Company and Bain & Company. Now builds Excel, other models and web applications for consultancies and other clients - and provides Excel training to consultants at all skill levels.

Leave a comment