This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Which File Format Should I Use?
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?
Figure 41 Excel 2010 also adds ODS.
Strategy: Excel 97-2003 typically stored files in a proprietary binary format. There are problems inherent with that format. For one thing, if a few bits get corrupted, you might lose all the data in the file. In addition, it is difficult for third-party programs to create binary .xls files.
Another problem was that 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 2010. However, this is not the default file format in Excel 2010. Microsoft has created something even better.
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 WinZip or any other 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 Microsoft Starter, Star Office, or 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 2007 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 five 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.