How Do Excel File Types Perform?
It’s probably not something you give much thought about, but awareness of the different Excel file types is important, so you can avoid limitations and data loss.
Before Excel 2007, the default file format was XLS (.xls); this was then superseded by XLSX (.xlsx). One major difference is the former is only capable of housing 65,536 rows and 256 columns, while the latter can handle 1,048,576 and 16,384, respectively.
Most of you today will save your files as XLSX; however, if your workbook contains VBA code, you will be forced to use a macro-friendly format — either XLSM (.xlsm) or XLSB (.xlsb). (You could use XLS as well, but don’t!)
XLSX and XLSM are XML-based formats, whereas XLSB is stored in binary form. The advantage is XLSB files are smaller in size and load faster.
I did a quick test to see how fast these file types loaded. Each workbook contained the same dataset — comprising one million rows and 15 columns of data. I also disabled all addons to reduce any additional startup time.
XLSX (.xlsx)
- 78.1 MB
- ~35 seconds
XLSM (.xlsm)
- 78.1 MB
- ~35 seconds
XLSB (.xlsb)
- 48.7 MB
- ~13 seconds
XLS (.xls)
- 13.7 MB
- ~4 seconds
Firstly, don’t be fooled by the XLS file’s performance — the row limit meant everything after the 65,536th row got cut off. Just remember, it was only in October 2020 when thousands of coronavirus cases went unreported because Public Health England embarrassingly used this very format for their system. 😳
The XLSX and XLSM files produced the same result, which shows merely saving a workbook as macro-enabled has no effect per se. Using XLSB though meant saving 29.4 MB and 22 seconds, making it the clear winner.
For small files, the benefits are negligible, but for larger ones like these, it’s difficult to argue the case for XLSX and XLSM.
Perhaps the security risk of macros might pose one concern, and a lack of familiarity could confuse the people you’re sharing the file with. It’s also not possible to customise your ribbon the advanced way, although this problem is easily circumvented by temporarily saving in an XML-based format.
If you don’t already, consider using XLSB. It’s underrated!
