The standard Excel workbooks can have one of two extensions: XLS or XLSX. The XLS format is the old one and was the default format before Excel 2007. XLSX is the newer one that was introduced in 2007; it is the default format for Excel 2007 and later. The question now is what is the difference between these two file extensions and why Microsoft chose to change the default file format for Excel?
There are many differences between XLS and XLSX:
- XLS format can be opened by all Excel versions; XLSX needs Excel 2007 or later.
- XLS stores data in binary format; XLSX uses XML.
- XLSX allows more rows and columns than XLS.
- XLSX supports tables whereas XLS doesn’t.
- XLS format can store macros, while XLSX can’t.
- XLS is less secure than XLSX.
- XLS takes more disk space than XLSX.
- XLS loads and saves faster than XLSX.
- XLSX is more reliable than XLS.
In the following, I will detail all the differences between these two Excel formats, XLS and XLSX; then, at the end of the article, I will tell you which format should you use, and will show you how to convert an XLS file to XLSX, or conversely, convert XLSX to XLS.
But, before that, I would need first to give you the meaning of XLS and XLSX acronyms:
- XLS stands for eXceL Spreadsheets.
- XLSX stands for eXceL Spreadsheets in XML format (more on that in the “File Format” section).
Now, let’s dig deeper into the differences between XLS and XLSX:
1- Excel versions and compatibility
- Versions: XLS file format was the default Excel format for versions between 97 and 2003; i.e. Excel 97, Excel 2000, Excel 2002, and Excel 2003. Then, with the launch of Excel 2007, Microsoft introduced the XLSX format which became the default file format for Excel 2007 and later.
- Compatibility: XLS format is compatible with all Excel versions, while the XLSX format is not compatible with Excel 2003 or earlier versions, and can only be opened with Excel 2007 or newer.
2- File format
The main difference between XLS and XLSX is the file format:
- XLS stores data in a binary format using the “Binary Interchange File Format (BIFF)” that is proprietary to Microsoft.
- XLSX uses Office Open XML format to store data in compressed XML files (XML stands for eXtensible Markup Language). Excel compresses the file when it is closed, and uncompresses it when opened.
Unlike XLS that uses a proprietary binary format, XLSX uses an open text format, which allows better compatibility with other applications and gives the user the ability to view the structure of the content of any XLSX workbook.
3- Volume of data / Number of rows and columns
XLSX can hold a larger volume of data than XLS, because of the number of rows and columns.
An XLSX workbook has 1,048,576 rows and 16,384 columns; whereas an XLS workbook has only 65,536 rows and 256 columns.
4- Tables support
Excel tables is a feature that allows using data in an organized, dynamic, and relational way.
They were introduced from Excel 2007. So, they are available only in XLSX workbooks; an XLS workbook can’t have an Excel table in it.
5- Security and support for macros
XLSX is based on an open format (Office Open XML), which makes XLSX files transparent since you can easily check any sensitive information in their content, unlike the XLS format which is an “obscure” one.
In addition, the XLS and XLSX formats differ in their support for macros (codes created by users to automate tasks):
The XLS format supports macros. So when you receive an XLS workbook, it may or may not contain macros. The XLSX format, on the other hand, does not support macros; this means that if you have an XLSX workbook, you are 100% sure that it does not contain a macro. Microsoft has dedicated a separate format for this, which is the XLSM format.
Therefore, and considering these two aspects, it is clear that the XLSX format is more secure than XLS.
6- Size of files
XLSX format is compressed by default; so, the size of a workbook is smaller in the XLSX format than in the XLS one.
For example, I made this “experiment”:
I created a blank Excel workbook and saved it in the two formats, XLS and XLSX. Here are the results: the size of the XLSX blank workbook is 9 KB, while the XLS blank workbook is 25 KB!
Also, I added some data and charts and compared the two formats: The amended XLSX workbook is now 33 KB, while its XLS equivalent is 63 KB!
So, compared to an XLS workbook, an XLSX one with the same content will take up less space on your device, and will therefore allow faster transfer across networks.
7- Speed performance
XLS format is faster than XLSX one; the difference in speed is more noticeable when you have a lot of data with complex formulas.
I made the following experiments to test this:
– First, I compared the opening speed of a blank workbook (with 01 sheet), saved in the XLSX and XLS formats.
– Second, I compared the opening speed of a workbook containing 2 sheets, each with more than 53000 rows and IF/AND formulas, in the two formats, XLSX and XLS.
Here are the results:
- Opening speed of a blank workbook (01 sheet):
– XLSX format: 2 seconds.
– XLS format: 1.5 seconds.
- Opening speed of a workbook (02 sheets, each with more than 53000 rows and IF/AND formulas):
– XLSX format: 5 seconds.
– XLS format: 2.5 seconds.
This is because of the structure of the format itself; for an XLSX file, Excel will need to uncompress the data, then open the workbook, which takes time compared to just opening the file in the case of XLS format.
The speed difference of opening an XLS file versus an XLSX one might seem not huge, but still. Imagine having a workbook with thousands of rows, so many spreadsheets, and multiple complex formulas.
8- Reliability of files / Data recovery
Since the Office Open XML format of XLSX workbooks is a package of distinctive parts in a text format, it is by nature more reliable than the binary format of XLS workbooks. Thus, even when data corruption occurs, the XLSX structure of separated text parts makes it easier to recover these distinctive parts than a binary whole.
So, that was the last aspect of the differences between the two Excel formats, XLS and XLSX.
As a conclusion of this comparison, we can say that each format has its benefits and drawbacks, with some advantages for the XLSX format.
The question now is which one to choose between the two. That’s what we will see in the following section.
Which one to choose: XLS or XLSX format?
The answer to the question of which Excel format to choose when working with workbooks, XLS or XLSX, is it really depends on the situation and your goals:
|When to choose XLSX format||When to choose XLS format|
|If you’ll need more than 65,536 rows or 256 columns!||If you want to share the Excel workbook with someone that has Excel 2003 and hasn’t the Office Compatibility Pack, or want to use it with an application that requires XLS format.|
|If you want to use Excel tables feature.||If the performance and speed matter for you, especially when dealing with large workbooks and complex formulas.|
|If you want an Excel workbook without macros.|
|If you want to have the ability to check the source code of the workbook.|
|If File size matters for you (e.g. you don’t have enough disk space or you want to share the workbook over Internet).|
|If you want to work with a reliable format and recover your files better after a data corruption.|
How to convert XLSX to XLS, or XLS to XLSX
To convert an Excel workbook from XLSX to XLS, go to File (Excel 2010 and later) or Office Button (Excel 2007), and click on “Save As”. In the “Save As” dialog box, choose where you want to save your workbook, then click on the drop-down “Save as type”, choose “Excel 97-2003 Workbook (*.xls)”, and click on “Save”.
To convert an Excel workbook from XLS to XLSX, go to File (Excel 2010 and later) or Office Button (Excel 2007), and click on “Save As”. In the “Save As” dialog box, choose where you want to save your workbook, then click on the drop-down list “Save as type”, choose “Excel Workbook (*.xlsx)”, and click on “Save”.