While discussing the multitudes of options and functions that exist within Excel, in order to truly master the program, it is necessary to discuss a few features outside of the program itself. One of these features is the various different file formats that a spreadsheet can be saved in. With over 20 default formats built in, and countless more that are not listed, knowing which format to use at the right time is very important. We will discuss each of these many options and when to use each one, as well as some extra options inside of Excel pertaining to file formats.
Saving a document should be pretty standard for anybody that has used a computer before, as the process hasn’t really changed over the lifetime of computers. For most programs, including Excel, selecting File in the toolbar and selecting Save As (or Save if the file is still unsaved) opens a browser window with the options to save the file. There are three primary options here: the browser to select the directory to save in, the name of the file, and the file format. This article will focus on the file format argument.
When the drop-down arrow of the file format box is selected, many different options are shown, as can be seen in the following Figure.
The default file format for Excel 2007 and later versions is the .xlsx format. This is the format that will be used most often when saving a standard spreadsheet, however, this format does have some limitations. First, this format is based on Excel’s XML format, and cannot be read by any program that does not use XML. This problem can be circumvented by saving the spreadsheet with the extension .xlsb, which will save the file as a binary-based file instead of XML-based. The .xlsx file type is also incapable of executing any VBA Macros used in the spreadsheet. If macros need to be enabled, the file should be saved with the .xlsm extension.
Excel files can also be saved as template files. When a template file is opened, instead of opening the file in a new window, the contents of the template are copied into the current file. This is very useful for creating new documents that use the same format, instead of having to start from scratch. The default extension of a template file is .xltx. Like the default spreadsheet format, this extension cannot be used for macros. In order to enable macros for a template file, the extension .xltm should be used.
Versions of Excel previous to 2007 use a different formatting system. If the file will need to be opened on one of these versions of Excel, none of the above-listed file types will work. The default format for spreadsheets before 2007 is the .xls format. Likewise, for earlier versions of Excel, the file format for templates is .xlt.
It is also worth noting that OpenOffice, the other most popular office suite, uses a different file format, the .ods format. These formats are cross compatible. OpenOffice has the ability to use Excel’s .xlsx (as well as .xls) format, and Excel has the ability to open OpenOffice’s .ods format, with very few compatibility issues.
All of Excel’s primary spreadsheet file formats are summed up in the following table.
While the .xlsx is the most used file type for the majority of Excel users, making it a good default, it may be beneficial to change the default format for many users, for example, if macros are used more often than not. To change the default file type, first select Options from the File submenu. Next, select the Save option from the menu on the right. A screen such as the one seen in the following Figure will be seen. In the drop-down menu for the ‘Save files in this format’ option, any file type supported by Excel can be selected for the default file format.
Knowing how Excel’s various file formats work is very important for cross-compatibility reasons, as well as to enable several select functions not supported by Excel’s default spreadsheet format. While spreadsheets can also be saved in several other formats, such as .txt and .pdf, knowing the basis for Excel’s spreadsheet formats will provide many uses to the average Excel user.