I remember when I started my career in FP&A (Financial Planning & Analysis), the preference by my then Director was for financial models that were clean, lightweight and free of codes. Before I was transferred to the FP&A unit, I had worked as an Investment Banking Analyst at one of the largest Investment Banks in my country, and I later moved into the telecoms industry and worked as a Strategy Analyst and Finance Business Partner. So I was used to writing VBA codes to achieve one or two things but the communication I received by the new director was no more codes.
Why, you may ask. Well, his rationale is that he wants flexibility in his team, the person that writes a code in one of his models may be on leave or even resign from the company and he doesn’t want to be in a position where he is begging the creator to adjust the model in either of those two scenarios.
The irony however was that he still wanted to achieve the complexities of what a model with VBA codes would achieve. This meant I had to write out and use plenty of complex array formulas, define names and implement numerous conditional formatting from the get go whereas these steps could have been implemented later with VBA codes and allow for me to save the workbook at a reduced size.
Why did I need to save the models at a reduced size? Because we were sharing as is to the CEO of the company and the IT team implemented a max outgoing file size of 5mb. Cant seem to remember the reason why but that was the policy then. One of the models we worked on was a 10 year business plan with 24 tabs with about 10 of them having the ability to standalone as a separate model, but because the plan would be audited by Big 4 model auditors, we chose to bring everything into a single model to reduce the back and forth because time wasn’t on our side. Of course, when we initially prepared the model in xlsx format, it was way above the maximum size of 5mb and I needed to reduce the size by all means.
To arrive at a size below the stipulated 5mb, one of the things I did (would discuss the other things in a later post) was to save the workbook in binary XLSB file format and this alone reduced the size by about 33%. Without further ado, let’s begin with what a binary file is
What is Excel’s Binary XLSB file format
Like the XLSX format (default format used in Microsoft Excel), the Excel XLSB format is a collection of records and structure that specifies excel workbook content. Such content may include text, numbers, formulas, external data connection, charts and Images. The major difference however is that whilst the XLSX files is compressed and stored in readable XML code, the XLSB is a zip container with many of its parts compressed binary components encoded in a proprietary format. They are optimized for performance (loads and saves large data files as fast as possible) but unreadable in many third party applications.
Advantages of Excel’s Binary XLSB File format
- Reduced file size: As already noted, the same file stored with the XLSB file format can be significantly smaller than that stored with the XLSX format. This could be due to the difference compression method. When excel wants to save as XLSX, it breaks down the file into separate XML, compresses it and then saves it as an XLSX file. Whereas for the XLSB, excel compresses at once the binary components and dumps it into a binary file, then saves as XLSB.
- Opens and saves quickly: XLSB file format was created for users who need to load and save data as fast as possible. The extra steps involved in compressing and savings XLSX (discussed above) makes it much slower compared to XLSB files when opening and saving
- XLSB can handle very long and complicated formulas better as other formats are limited to a maximum of 8192 characters
Disadvantages of Excel’s Binary XLSB File Format
- XLSB is not supported by older versions of excel: Microsoft Excel 2003 and earlier versions do not support the XLSB file format. This is less of a concern as most people typically use Microsoft Excel 2007 and above.
- Potential Vulnerability: Attackers may send excel files with malicious code as XLSB file because it can store both workbooks containing code and without code as opposed XLSX and XLSM with only the latter containing macros
- Compatibility with third party tools: XLSB files are not compatible with a number of third party tools so you need to ensure that the tools you intend to use are compatible with the XLSB file format if you plan on saving your file in that format
Steps to Save in Binary XLSB Format
To save your document in the XLSB format, follow these easy steps
Step 1: Navigate to file and select save as
Step 2: Click on browse to select the location you want to save your file in
Step 3: Select Excel Binary Workbook and click save at the bottom right
