Small Business

Exploring the Nuances of Excel Workbook Formats

XLSB

The Excel spreadsheet Binary format has all the characteristics of the XLSM format, but often has a much smaller file size. The text-based nature of the XML formats mean that large spreadsheets can often balloon in size. Conversely, binary files are a collection of ones and zeros that are often referred to as “machine code,” meaning typically only the computer can make sense of the document format.

The XLSB format is ideal for large spreadsheets, especially those in the 10 MB to 15 MB size range. Often, you’ll be able to save an existing Excel workbook in the XLSB format and see roughly a 33% reduction in the file size. The binary nature of the files means that such files often open faster than the XML-based versions. The trade-off is that you may not be able to open an XLSB file in other spreadsheet applications, nor can you determine in advance if such a file contains macros. As noted, the XLSX format is an immediate indication that no macros reside within the file. As shown in Figure 5, saving a 4.48 MB XLSX file as an XLSB file resulted in a 37% reduction in file size.

Open File

Figure 5: The XLSB file format often shrinks files sizes by 1/3 or more.

As a bit of trivia, you can poke around inside an XLSX or XLSM spreadsheet if you wish. To do so, right-click on the file name within a My Computer or Windows Explorer window and choose Rename. Replace XLSX or XLSM with ZIP, and then press Enter. Choose Yes when warned about changing the file extension. You can then double-click on the resulting file to see its contents. Double-click on the XL and then Worksheets folders to see the worksheets within the file. Bear in mind that you’ll be looking at XML code, so much of it will look incomprehensible, but this can be a last resort method to try to extract data from a damaged Excel workbook.

XLS

The Excel Binary File format harkens back to the early days of Microsoft Excel. Early on, such spreadsheets had 16,384 rows. Starting with Excel 97 the XLS format offered 65,536 rows, which is why this is often referred to as the Excel 97-2003 file format.

Although you can use such spreadsheets in Excel 2007 and later, I strongly discourage you from doing so unless you have a specific need to preserve that old format. The modern versions of Excel utilize what is known as “Compatibility Mode” when you open such files. You can freely open, edit, and save XLS files, but you may notice diminished functionality in Excel.

For example, in Excel 2013 and later two of the three controls that appear to the top right of a chart that you select will be disabled. Other features, such as Sparklines, Slicers, and Timelines will all be disabled. This is intended to protect you from performing work on the spreadsheet that cannot be saved to that obsolete file format.

The best action to take when you open an XLS file will typically be to immediately convert the document, as shown in Figure 6. Note that the Convert command only appears when needed, meaning that if you don’t see Compatibility Mode at the top of your Excel screen, you also won’t see the Convert command.

Convert Command

Figure 6: The Convert command transforms XLS files into either XLSX or XLSM files and unlocks all of Excel’s functionality.


Save pagePDF pageEmail pagePrint page

About the author

David Ringstrom

David Ringstrom, CPA, is the president of Accounting Advisors, Inc., an Atlanta-based spreadsheet and database consulting firm he started in 1991. Throughout his career, David has spoken at conferences on Excel, and he currently leads dozens of webinars each year on Excel, QuickBooks, and other software. He has served as the technical editor for over 25 books, including several editions in Wiley’s QuickBooks for Dummies and Quicken for Dummies series. In addition to writing for QuickBooks and Beyond, David is the Tech Editor at Large for AccountingWEB and Going Concern. He also offers live webcasts and self-study courses through CPE Link. His freelance articles on spreadsheets have been published as far afield as Pakistan. During training sessions, you’ll often hear David state, “Either you work Excel, or it works you!”

4 Comments

Leave a Comment