Which File Format Should I Use?

August 02, 2021 - by Bill Jelen

Problem: I’ve been using .xls files for years. What are these new .xlsx, .xlsm, .xlsb, xlam, and .ods file types? Which should I use?

Strategy: Excel 2003 used XLS, a proprietary binary format. The old .xls binary file format could not handle data beyond row 65,536. So, the new .xlsb file format is a proprietary binary file format that can handle the 17 billion cells in Excel.

The new .xlsm file format is an amazing file format. The entire spreadsheet is saved as a series of text-based XML files, and then that collection of files is zipped into a single file in order to save disk space. You can actually take a look at the insides of an .xlsm file. In Windows Explorer, if you rename the file and add a .zip extension, you can then open the file using any zip utility. This is a fairly exciting advancement because it means people will be able to use third-party tools to generate Excel files without having Excel on their computers. However, the .XLSM file takes a bit longer to open than .XLSB. My preference is .XLSM, but if you have particularly large files to open, then .XLSB might save you some time.

You can tell that security issues have taken a grip on the people at Microsoft. They’ve introduced a new file format that guarantees that there will be no macros inside. The .xlsx file format uses the same zipped file structure as .xlsm but deletes any macros in the file. As someone who uses macros all the time, I think this is a silly file format. I guess if you plan on doing everything manually in Excel and if you never have any plans to learn how to dramatically increase your efficiency with Excel, then you could adopt the .XLSX file format. Actually, if you fit into this category, you could use Google Docs!

.xlam is another new file format. Developers can deliver Excel add-ins in this file format.

.ODS is a relatively new (2007) file format used across many different spreadsheet programs. Originally developed by Sun Microsystems, there is now a committee who oversees this format. It still isn’t quite perfect, as Microsoft points out that you can not robustly store track changes information in this format. If there are pockets of people in your company who are using spreadsheet programs other than Excel, this might be a good format for you.

In case you are working in an office where many people still use legacy versions of Excel, you can always use the Save As command to save an Excel file as an Excel 97-2003 file format. Excel actually supports saving to 27 different file formats, including CSV, DIF, SLK, and other specialized formats.

Additional Details: You will probably choose one file type and stick with it. I’ve been using .xlsm files without issue for seven years. If you decide on one format, you can tell Excel to always use that file format. To do so, you select File, Options, and in the left pane of the Excel Options dialog, you choose the Save category. From the top dropdown, you select your favorite file format, as shown here.

This article is an excerpt from Power Excel With MrExcel

Title photo by Thiago Rocha on Unsplash