XLSB as a faster alternative to XLSX/XLSM
Last week a client came to us, said their spreadsheet was running slowly and that they'd been quoted over $10,000 to build a more streamlined version. Our consultant took one look at the file, saved it in a different format and applied our minimum charge. So, what was the magic format, and can it help you?
The format was XLSB. If you open a post-2007 version of Excel, it's the third option on the dropdown list where you can select your file format. The first is the default XLSX, then there's XLSM which is identical to XLSX except it can contain macros, then XLSB appears just above XLS which is Excel's legacy file format. What is it?
(If you aren't interested in technicalities, skip this paragraph). The clue is in Excel's history. XLS files were the old file format and stored data in a binary format. As computers were slower, this represented a very efficient way to store the data. Microsoft then decided the future was online and wanted a more web-friendly format so, in Excel 2007, they switched to an XML format which would allow web servers to read data from within Excel spreadsheets. Also, Excel was becoming richer so there were a lot more styling parameters to store. However, Microsoft also upgraded their binary format and introduced auto-compression on saving, giving us the XLSB format.
The key message is that XLSB files are like the old XLS files but they can support all the features of Excel 2007/2010/2013 (the last one is currently a guess but I see no reason it wouldn't), and are more efficient. By way of example, I took a 2.3MB xlsm file, saved it as an XLSB file...and now it's only 1.4MB, so we've made the file 30-40% smaller. That file is heavily styled and is 3.8MB in the old XLS format.
If I look at a data-rich file, it can be 6.4MB in XLSM format, 4.4MB in XLS, and 3.0MB in XLSB format. We've halved the file size by saving in XLSB format and the best bit is, it still runs perfectly. Which raises the question of why you'd ever use any other format. I guess I can come up with a few reasons:
- XLSX files are familiar to people who are unfamiliar with Excel. An XLSB file might cause confusion
- XLSM files contain macros, XLSX files don't. XLSB files don't give any indication until you open them. An intermediate user might want to know whether to expect code before opening a file
- You only get a performance benefit with complex files. The XLSB format isn't designed to work with data servers. That's fine if you are always going to open your file in Excel but if you wanted to access the data through the web, it could cause problems. If all spreadsheets are ultimately moving to the cloud and going to be accessible on all platforms, why introduce an extra hurdle to the migration?
...beyond that I'm struggling. The first two don't apply if users hide file extensions in Windows, and the third can be resolved by opening the file and re-saving in XLSX/XLSM format. I certainly wouldn't shy away from saving a spreadsheet in XLSB format if I wanted to make it run faster. Although you could make it your default file format, it's probably not worth it for everyday use.
