Best Excel file format for Excel VBA Programming

The various Excel File Formats

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 of Binary Excel File Format

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.

best excel file format experiment

Experimental Results of Excel file formats

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!

best excel file format results
Advanced Excel Training Courses Classes Singapore: Basic VBA Programming

Choose from our Most Popular Courses

Basic VBA Programming Course with Microsoft Excel. Designed for non VBA practitioners. This is a short & easy-to-follow workshop to learn how to build VBA programs from scratch in Microsoft Excel.

Consider choosing from our list of effective Microsoft Office training courses such as Excel Training Courses and PowerPoint Training Courses by Aeternus Consulting.

Useful Resource: Get Microsoft Excel for free here.

Leave A Comment