Best Excel file format for Excel VBA Programming

Back in the days of Excel 2003, saving a workbook file is simple. You only save in .XLS file format.

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 FormatFile ExtensionDescription
Excel Workbook.xlsxThis 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.xlsmIf 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.xlsbThe 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
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
best excel file format results

Leave a Reply

Your email address will not be published. Required fields are marked *

Contact Us

Drop us a message even when it’s late because the best ideas can come at night.

About Aeternus Singapore

aeternus consulting singapore logo
Aeternus Consulting is the premier consultancy firm for analyst-grade Microsoft Office training courses in Singapore. ACRA: T14LL0891K

Microsoft Office training solutions

corporate training course chair
We work with businesses and individuals who need effective Microsoft Office training solutions.

Analyst-Grade Training Courses

analytics with microsoft excel chart
Premium Training for Business Analysts, Data Analysts & Information Specialists in Singapore.

Our Services Include

Our Microsoft Office-centric services include:
* Full range of Microsoft Excel Training Courses in Singapore.
* Microsoft Office PowerPoint Training Courses.
* Consultancy in Excel, Access, VBA Application Development & PowerPoint Presentation Design Projects.
* Personal coaching & corporate bespoke Microsoft Office training sessions for employees.

Training Course List

Excel Basic, Intermediate, Advanced Levels
Excel PivotTables Inside Out
Data Management & Data Analysis in Excel
VBA Programming with Excel
Advanced VBA Programming with Excel
Essential Microsoft Excel for HR Professionals
Up to Speed with Microsoft PowerPoint
PowerPoint Design & Animation Ideas for Business
Microsoft SharePoint Training Workshop

Instagram (@aeternusconsulting)