Binary files the key to smaller Excel files

Question

Is there a way to reduce the size of an Excel file?

Answer

There is an under-used file type that might be worth trying. Whether it is successful will depend on how large and complex your file is. In Excel 2007, Microsoft introduced three new file types.

Two of them use XML (Extensible Markup Language) and data compression. That means files are comparatively smaller than previous versions. The third file type also reduces file size, but it has a few downsides.

The first XML file type – .xlsx – does not allow macros. If you create a macro in this file type and try to save the file, Excel will display a message warning you that the macros will be removed.

The second XML file type – .xlsm – is macro-enabled and allows you to include macros in the file. In the third file type – .xlsb – the “b” stands for binary. The binary file type is reasonably efficient and offers some size and speed advantages, but it also has compatibility limitations.

Binary file advantages

1. Reduced file size in some situations. Files above 10MB show the greatest reduction in file size.
2. File opens faster than XML file types.
3. File saves faster than XML file types.
4. File can contain macros.

Binary file disadvantages

1. File may not interact with third-party products that require the XML format. If you need to upload an Excel file into another system, the binary file type may not work or be accepted.
2. File may not be compatible with Excel 2003 and earlier versions (this may be why it was overlooked when it was first introduced).
3. File may not be compatible with other spreadsheet systems, e.g. OpenOffice or other spreadsheet applications.
4. File can contain macros.

Notice that I have included “File can contain macros” in both lists. Your views on macros will decide which entry you agree with.

If your Excel files are not interacting with third-party products or older versions or other spreadsheet systems, then using the binary file type could be worth experimenting with.

Even if you do need to interact, you can still use Save As to save the binary file as one of the other XML file types for export, or use in other systems.

The keyboard shortcut for Save As is the F12 function key. This shortcut works in all Microsoft Office applications. Figure 1 shows the binary option in the Save As dialog.

Reduced file size and faster opening makes the binary file type an attractive alternative. 

The table in Figure 2 shows the difference in results between the two functions. The green shaded cells are the cells counted by each function.

The formula in cell B13 is:
=COUNT(B2:B12)

The formula in cell C13 is:
=COUNTA(C2:C12)

The general rule is that the COUNT function only counts cells containing numbers. Excel holds dates as numbers, so dates are also counted. Be careful when using the COUNT function if you select ranges that include date headings. The COUNTA function counts every cell that has an entry, even if it appears blank, see note below.

Note: formulas that display a blank cell using double quotation marks (see the formula example below from cell B8 in Figure 2) will be counted by COUNTA but not COUNT.
=IF(B3>0,””,0)

The formula has been copied across to cell C8. If this formula displayed the 0, then COUNT function would include it.

Sometimes Excel can have a number entered as text, see row 6 in Figure 2. COUNT ignores these cells, whilst COUNTA includes them. Imported data can often include numbers formatted as text.

The companion video and an Excel file may assist your understanding.

Function tip: counting on Excel

Excel has two basic counting functions. The COUNT function counts only numerical values. The COUNTA function counts everything. I treat the "A" in the end as mean ALL. In most cases, you need to COUNTA when you are counting.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]