Difference between XLSX and XLSM and XLSB
| .xlsx | .xlsm | .xlsb | |
|---|---|---|---|
| File format | XML based | XML based | binary based |
| Support macros | no | yes | yes |
| File size | normal | normal | smaller |
| File open | normal | normal | faster |
| File save | normal | normal | faster |
XLSB advantages (XLSB VS. XLSX / XLSM)
- Smaller file size – significantly less space taken by Excel binaries, especially when working with large Excel files.
- Opens/saves more quickly – Loading binary data is faster than parsing text (XML) files. Also, when saving files, the binary format is more efficient than dumping the data back to XML and then compressing it.
XLSB disadvantages (XLSB VS. XLSX / XLSM)
- Compatibility – the XLSB Excel format is not supported by Excel 2003 and earlier.
- Macro Security – By distinguishing between XLSM and XLSX formats, you know which files may or may not contain VBA macros. With XLSB, you can't be sure. So, be careful while opening XLSB files from unknown sources or people/websites that you don't trust.
The myth of XLSB
- Custom Ribbon is not available in XLSB files: Wrong.
- XLSB files are not compatible with LibreOffice or OpenOffice: Wrong.
- XLSB files cannot be used in Power Query: Wrong.
- Formulas in XLSB files are recalculated faster: Wrong. Excel data structures and algorithms that reside in memory are not affected in any way by the file structure used, except for open and save operations.
My advice
In daily work, I recommend sticking to XLSX and XSLM as standard Excel file formats. Whenever your files start to run slowly or take up a lot of space, it's worth using the XLSB file format.