Hello all and welcome back to the Excel Tip of the Week! This week we have a Basic User post in which we are taking a fresh examination of the common file types that you can save an Excel spreadsheet as – and what each means.
This was first discussed in TOTW #258.
Above is the full list of the file types that my Excel 365 offers from the “Save As” menu. This is quite a bewildering selection at first, but many of these are very obscure and you’re unlikely to ever need to use them. We’re going to focus on the most common and most useful, explain the differences between them, and when you’d want to use each.
Excel workbook – .xlsx
This is the default file format for all Excel versions from 2007 onwards. It’s actually a kind of compressed file – a series of connected XML files representing the different parts of the file, that are assembled into the complete file when you open it. It is possible to see the guts of an XLSX file if you change the file extension and run it through an unzipper!
An XLSX file is secure and stable, and is unlikely to be fully corrupted and unreadable. The format is also accessible by other spreadsheet software.
Excel macro-enabled workbook - .xlsm
XLSM is identical to XLSX, except for that it also allows the file to store VBA code – whether in the form of macros or user-defined functions. This uses a separate file type to make quarantining potentially hazardous VBA code simpler – no XLSX file can contain macros, so many organisations block XLSM files or only allow them to be opened with limited permissions. If your file does not have macros in them, you should always save as XLSX to avoid any confusion.
Excel binary workbook - .xlsb
This is a simpler way of saving Excel files that does not use compressed XML files, but just saves the workbook in a single flat file. Typically an XLSB will be smaller than the XLSX version of the same file. However, XLSB files are more easily corrupted as they have only a single file to work with, and cannot be opened by third-party software. XLSX is usually a better bet.
Excel 97-2003 workbook - .xls
This is the original Excel file format. It is considerably more limited than a XLSX – for example allowing only 65,536 rows and 256 columns, instead of the 1,048,576 rows and 16,384 columns that XLSX allows. Data beyond these limits will simply be lost. This file type is deprecated and may lead to loss of data or formatting, sometimes without warning. Do not use it unless you are for some reason working with pre-Excel 2007 users.
Excel template - .xltx
An Excel template file is used by Excel to create new workbooks or worksheets. If you want to change the default new workbook or new worksheet that Excel creates when you open a fresh file or tab, then you can do that by saving your design as an XLTX file in the Excel startup directory with the name Book or Sheet respectively. The default location for that file will be opened as soon as you select XLTX from the Save As menu; it’s typically somewhere along the lines of C:\Program Files\Microsoft Office\root\Office16\XLSTART.
CSV (Comma delimited) - .csv
One of the simplest possible file formats, CSV consists only of values separated by commas, with extra commas to mark the ends of rows. Many programs use CSV as a simple export/import format, so it’s not unusual to get data in this format, or to need to save in this format to enable an upload. But the format is extremely limited, allowing only data with no formatting, no formulas, and only one sheet. If you are working with a CSV source and plan on adding anything to it, make sure you remember to save it in a different file format as soon as possible to minimise the risk of losing your work. And likewise if you need to save a CSV version of a more complex file, save it in a more fully-featured format first.
Note that there are also Macintosh versions of the CSV format available.
Excel Add-in - .xlam
An Excel Add-in is an Excel file containing VBA code that you can add to your Excel installation to simplify gaining access to routines or user-defined functions that you need to use frequently. It’s essentially the same as an XLSM file except for that it allows you to add it to Excel using the Excel Options => Add-ins menu.
Check out TOTW #410 for more on how to use custom Excel add-ins.
Having an idea what kinds of files are out there for Excel to work with and save in can be really handy – so keep these in mind next time you’re thinking about saving your file.
Join the Excel Community
Do you use Excel in your organisation? Are you using it to its maximum potential? Develop your skills and minimise spreadsheet risk with our Excel resources. Membership is open to everyone - non ICAEW members are also welcome to join.
- Excel Tips and Tricks #496 – ‘Check Performance’ in Excel
- Excel Tips and Tricks #495 - Excel “Tick”ery!
- Excel Tips & Tricks #494 - How to add a custom ribbon to your workbook part 3
- Excel Tips & Tricks #493 - How to add a custom ribbon to your workbook part 2
- Excel Tips & Tricks #492 - How to add a custom ribbon to your workbook
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
