When creating Excel workbooks there are a large number of file type options to choose from. Some are best reserved for the importing and exporting of data e.g. TXT, CSV, PDF, and some for specific/legacy functionality e.g. templates, add-ins, and even the legacy XLW Excel 4.0 filetype which can be opened in Excel 2013 and later but not saved.
There are several 'standard' file types for day to day workbooks, each with their own advantages and disadvantages.
XLS - the binary file type for Excel 5.0/95 and 97-2003. You're still likely to encounter these on a regular basis. Where possible they should be converted to a newer file type to both save disk space and take advantage of newer Excel functionality. The only benefit of this file type is its backward compatibility with older versions of Excel (2003 and prior). Its disadvantages are a larger file size and smaller row/column limits than newer formats (65,536 rows and 256 columns), and no ability to use newer functionality. It's also not possible to tell from the XLS file extension whether the workbook contains VBA macro code.
XLSX - the XML file type. The default file type for Excel 2007 and later, documents are saved in an XML (Extensible Markup Language) format. It's compatible with all formula functionality (which can cause workbook formula to 'break' when using older Excel versions) and supports larger sheets than XLS (1,048,576 rows and 16,384 columns) but does not permit the use of VBA macros. The file size of a workbook is smaller when saved as XLSX instead of XLS - whilst storage tends not to be an issue these days, this could be important when trying to email files and stay within a corporate environment's attachment size limit.
XLSX is an 'open' file format which means it's more likely to be read correctly by other spreadsheet software. A workbook saved in the XLSX format is in fact a collection of text files - if you change the file extension from XLSX to ZIP (do this only on a copy of your workbook!) it can be opened in a ZIP file reader to view the internal file structure. I used this 'feature' once to identify which sheet in a workbook was causing it to take far longer than necessary to open: By viewing the text files I could see that one worksheet was significantly larger in size than the others yet looked identical in Excel. It transpired that millions of empty cells had formatting applied - a quick fix when you know where to look!
XLSX Strict - the 'even more compliant' file type. This appears in the Excel file type list as "Strict Open XML Spreadsheet (*.xlsx)" and, as the name would suggest, it complies more strictly to the Open Document standards. It isn't compatible with some of Excels newer and unique features and therefore not widely used.
XLSM - the macro compatible file type. Identical to the XLSX file format but permits the use of VBA macros. Users can identify from the file extension that macro code is embedded and take any necessary security precautions when opening/using the workbook.
XLSB - the latest binary file type. File sizes are even smaller than XLSX and there are also considerable speed gains in opening and saving binary files, compared to the XML formats, when using large and complex workbooks. Functionality is the same as the XLSM file format, since VBA macros can be embedded, so precautions must be taken as the file extension will not indicate it contains macros.
ODS - the OpenDocument Spreadsheet file type. Use this format only if you require seamless interoperability with open source spreadsheet software such as LibreOffice and OpenOffice as there are differences between ODS and XLSX which cause compatibility issues with newer Excel functions and features.
My preferred choice is to use XLSB because of the speed and file size benefits. The only downsides are compatibility with other spreadsheet software (unlikely in most corporate environments) and the precautions that must be taken with VBA macro code. From Excel 2007 onwards the default file type is XLSX but this can be easily changed - in Excel 365 for example, choose "File > Options > Save" and select your preferred default.
On a final note, if workbook file size is an issue for you then this article provides some good ideas on how to make your spreadsheets smaller.
