The various Excel File Formats
Best Excel file format for VBA: Back in the days of Excel 2003, saving a workbook file is simple. You only save in .XLS file format. However, starting from Excel 2007 saving a workbook got more complicated because there quite a number of formats you can choose. I’m going to focus on the 3 most important Excel file formats to the regular Excel user, and finally give an answer to which is the best Excel file format to use.
| Excel File Format | File Extension | Description |
|---|---|---|
| Excel Workbook | .xlsx | This is the default XML-based file format for Excel 2007 and beyond. It’s good for storing regular workbook data but it cannot be used for Microsoft Visual Basic for Applications (VBA) macro codes. |
| Excel Macro-Enabled Workbook | .xlsm | If you are trying to save VBA codes with your workbook in Excel 2007 and beyond, you must use this .xlsm format or Excel binary file .xlsb format. |
| Excel Binary Workbook | .xlsb | The binary file format (BIFF12) for Excel 2007 and beyond. |
The Advantages
The XLSB binary format is a full-fidelity file format that can store anything you can create in Excel, but the XLSB format is optimized for performance in ways that aren’t possible with a pure XML format. Internally it is a ZIP compressed file and this reduces the disk space required to store files and decreases the bandwidth needed to transport files through e-mail or networks.
The second advantage of the XLSB format is, it supports macro-enabled workbooks. That’s right, if you have VBA codes embedded in a workbook, saving your workbooks in XLSB instead of the usual XLSM format means your files can be smaller, and opens faster by your computer!
Experiment with the XLSB Excel file format
Here is a simple experiment you can do to verify these advantages.
Step 1. Create a normal Excel workbook at fill a huge range say from cells A1:Z500000 with anything.
Step 2. Save this workbook as a XLSX Excel file format, XLSM Excel file format and also as a XLSB Excel file format.
Experimental Results
The results seems to corroborate with the theory. The XLSX and XLSM Excel file format created files that is 33Mbyte, whereas the same data saved as XLXB Excel file format created a file that is only 2.5Mbyte. This is a 92% reduction in file size!
Opening the XLSX and XLSM Excel file format was painfully slooow. It took about 28 seconds. The XLSB Excel file format takes only about 6 seconds to open on my Intel i7 laptop. This measures about a 75% reduction in file opening times.
It’s obviously clear that XLSB Excel file format is definitely the best file format to work with!
Aeternus Consulting is the premier training centre in Singapore for Excel Courses – Basic Excel, Advanced Excel and Excel VBA Macro courses. For more Microsoft Excel training courses, please visit our Excel training page.
Aeternus Consulting is now on Instagram! So take a look and follow us on Instagram.
