When you save a workbook in Microsoft Excel, you can choose from a list of different file extensions (or formats).
Just click on the ‘Save As’ option to view the choices available. The resulting drop-down menu will list out all the file extensions along with their name descriptions.
This is how the window looks in Microsoft Excel 2019 (or Excel for Mac Version 16.66.1).
The number of extensions available will differ depending on the language version of the Excel you’re using. But the default file extension for Excel 2007 and onwards remains .xlsx
Here are the most commonly used file extensions in Excel.
The XLS extension was the default legacy Excel format till the release of Excel 2007. Today, the extension is rarely used.
It stores the workbooks in the Binary Interchange File Format (BIFF), which was proprietary to Microsoft. And so, you can open XLS files in older Excel versions like Excel 2003 (and the newer ones) without breaking a sweat. But you cannot manipulate the content of the file using external software or applications.
XLS is also not safe as a data source. This is because it can contain VBA macros, which gives leeway to malicious actors. As a result, they are more susceptible to malware and ransomware corruption.
As stated earlier, XLSX extension is the default type in Excel 2007 and onwards. But it is also supported in Google Docs, Apple Numbers, and OpenOffice.
In comparison to .xls files, the .xlsx files are more structured and open. They use the Office Open XML format, which is derived from XML (eXtensible Markup Language). This simply means that the data is stored in a plain text format.
As a result, you can save and share the .xlsx files independent of the software and hardware. Plus, they cannot save VBA macros, so they are safer than .xls files.
Moreover, every single cell in the XLSX file can be formatted individually. It can run and save all Excel functions as well. This makes the format highly interoperable.
CSV stands for comma separated values. It is a type of plain text file with the ability to store records of data separated by commas. Such that, the data is saved in a tabular format.
But, at the same time, CSV files can only save a single worksheet. They cannot save columns, rows, settings or formulas.
In Excel, you can import and open a CSV file directly. It comes with infinite number of rows and columns, but cannot contain macros or code. So, the files are safe to transport.
This is also why most programs support CSV extension, making it a go-to option for exchanging high volumes of data securely.
Open Document Spreadsheet or ODS extension is another XML-based format in Excel. It can store records of data in clean rows and columns. However, if you save a worksheet in Excel with the ODS extension, the formatting, repeat rows/columns, names, charts, tables, etc., can get lost.
But unlike the XLSX files, ODS files can be edited and manipulated on applications like NeoOffice and LibreOffice as well.
So, just by looking at the file extension, you will know if the file is based on XML documents or contains VBA. This is why knowing Excel formats is so beneficial.
However, the file extensions can reveal more than the version of Excel it was saved on. It can also tell you the following.
All Excel files with XLTX extension are a type of template file. And like the .xlsx extension, they too are based on the Office Open XML file format.
This file format serves as a standard template for generating multiple .xlsx files with the same functionality, settings, and layout information. Excel, itself, comes with many .xltx files for budget planning and task tracking. However, these files cannot be opened in the Excel 2003 version and beyond.
All Excel files with .xltm extension are similar in structure to .xltx files, but will contain all the recorded macros. This allows you to generate multiple files with macros included. But there is one condition. You will need to save the macro-enabled files with the XLSM extension. otherwise, Excel won't execute them.
What are macros in Excel? According to Microsoft, “a macro is an action or a set of actions that you can run as many times as you want.” This allows you to automate tasks you have to do repeatedly. It could be performing simple arithmetic or bolding a certain row of data.
Other than macros, XLTM files can contain layout information, settings, and spreadsheet data as well.
You can save a workbook in more than one format available in Excel. Or, you can add anywhere between 200 to 250 custom file formats of your liking. This makes the Excel files easy to import, export, and access.
Clearly, Excel is a great tool for storing, sharing, and manipulating large sets of data. But does the tool offer the same ease with data analysis? Well, not so much.
With a limit of 1,048,576 rows and tedious, time-consuming functions, you can only do so much with Excel. If you are looking for quick and easy data analysis, turn to Gigasheet.
Excel also isn't free. Generally it is purchased as part of an annual Microsoft 365 subscription. If you need to view the contents of an Excel file and don't have a subscription, load the file to Gigasheet! Gigasheet is *free for life* and does not require installation or configuration.
Here is how you can get started with Gigasheet.
To understand how you can explore and analyze data with Gigasheet, check out the resources below.
Author Bio: Payal Gusain leads growth initiatives at Ukti. When she is not weaving interesting narratives for clients, she is usually annoying her dog or dissecting lyrics by Frank Ocean.