There are certainly far more benefits in saving larger database files managed in Excel as the binary format suffix of .xlsb over any drawbacks.
Of course if it was 100% beneficial in every situation then Microsoft would have introduced this as the standard saving format, but the reality is that for a wider use and wider audience using Microsoft Excel everyday for any number of different tasks, the compatibility of .xlsx is the better. The majority of our Excel projects and bespoke applications are saved as the standard or macro enabled suffix but when it comes to the larger database systems we prefer to use, and recommend the .xlsb suffix. There are no noticeable visible or functionality differences between the 2 formats, however the icons for the saved files are noticeably different.
Macros and VBA are fully supported and the loading/saving times are far quicker. We refer to a recent forum user from Stackoverflow who carried out a number of tests on a generated worksheet with 10,000 rows * 1,000 columns = 10,000,000 cells of simple chained =…+1 etc formulas and reports:
.xlsx = 165s
.xlsb = 43s
.xlsx = 115s
.xlsb = 61s
.xlsx = 91MB
.xlsb = 65MB
The source machine stats are: Core2Duo 2.3 GHz, 4 GB RAM, 5.400 rpm SATA II HD on Windows 7. As technology gets better, these timings will improve for both formats, but we still believe the binary will outperform for a good while yet.
In comparison, the .xlsx takes 4 x longer to load, takes twice as long to save and is 150% bigger in size. When dealing with ever growing database systems this time saving can prove a real benefit as well as the size saving of .xlsb.
Calculations carried out in binary workbooks are quicker too. Where a database may not actually do many calculations it must be noted that Excel formulas can run on all cores of a processor, but VBA which is heavily used in databases will only run on 1 core at a time. The difference in speed offered by a binary file comes into it’s own in these instances.
However, there are a couple of drawbacks as there is a potential lack of interoperability with OpenOffice and any version before Excel 2003. There is no compatibility with Apples ‘Numbers’. These binary files are exclusive to Excel which means if you want to share the file with a non Excel user then you will have to convert or save as the standard .xlsx or .xlsm files. You will also be unable to customise the ribbon in Excel for the binary files.
So to summarise, we would use binary formats for our larger and more complex projects such as databases with excess of 10,000 rows or for databases likely to grow to that size and would remain with the .xlsx or .xlsm for those files that are standard projects, small to medium databases or require interoperability between operating systems and alternatives to Excel such as OpenOffice, Numbers etc.
Feel free to ask us a question through the contact form or leave a comment below for us to get back to you.