When working with Microsoft Excel, you might have come across two different file formats, namely XLS and XLSX.
Understanding the difference between these two formats can help you make better decisions when creating, saving, and sharing Excel files (especially when you need to share your work with people using different versions of Excel).
The primary difference between XLS and XLSX files lies in the underlying file format.
XLS files are based on the Binary Interchange File Format (BIFF) and store information in binary format.
On the other hand, XLSX files are XML-based with a more open structure, making them compatible with not only Microsoft Windows but also MacOS, iOS, and Android platforms.
This difference between .xls and .xlsx file format leads to some variations in features, file size, and compatibility, which you should consider when choosing between the two for your Excel projects.
Some advantages of using XLSX files include their generally smaller file size and reduced likelihood of corruption compared to XLS files.
However, XLS files may still be beneficial in certain situations, especially when working with legacy Excel versions that do not fully support the XLSX format.
It’s important to familiarize yourself with the pros and cons of each file format so you can make the best choice for your specific needs.
XLS vs. XLSX Format – At a Quick Glance!
| Aspect | XLS | XLSX |
|---|---|---|
| File Format | Binary Interchange File Format (BIFF) | Office Open XML Format |
| Compatibility | Readable by all Microsoft Excel versions | Readable by Excel 2007 and later versions |
| File Size | Generally larger | Smaller due to XML-based open file format and data compression |
| Security | Supports macros, which can pose security risks | Does not support macros, offering a safer environment |
| Worksheets/Cells | Supports up to 65,536 rows and 256 columns | Supports up to 1,048,576 rows and 16,384 columns |
| Formulas | Supports older Excel functions | Better compatibility with newer Microsoft Excel functions (such as IFERROR, XLOOKUP, TEXTAFTER) |
| Graphics/Charts | Limited to older Excel chart types | Offers more advanced options and compatibility with the latest Excel features and chart types |
| Macros | Can handle spreadsheets with or without macros | Does not support macros (use XLSM format for macro support) |
| Usage | Better for backward compatibility and working with legacy Excel versions | Better for security, reduced file size, and access to advanced features in recent Excel versions |
Also read: Row vs Column in Excel – What’s the Difference?
A Brief History of Excel File Formats
In the early days of Excel, there were different file formats developed to cater to the various features and compatibility requirements.
Understanding these formats will help you choose the right one for your needs.
Binary Interchange File Format (BIFF)
Before Excel 2007, Microsoft mostly used Binary Interchange File Format (BIFF) to save Excel files.
This file format had different versions like BIFF2, BIFF3, and BIFF4 that are associated with Excel versions 2.x, 3.x, and 4.x, respectively.
The most common BIFF file format, which is still used today, is BIFF8, linked to Excel 97-2003, and is saved as an .xls file.
BIFF formats store data as binary, which has some inherent limitations related to file size and compatibility.
BIFF formats are also less resistant to file corruption, making them less reliable than newer formats like Office Open XML.
Office Open XML Format
With the release of Excel 2007, Microsoft introduced a new file format called .xlsx, based on Office Open XML (OOXML).
This format provided various improvements over the older BIFF formats, such as:
- File size reduction using ZIP compression.
- Increased row and column limits in worksheets.
- Better compatibility with other file formats.
- Improved file recovery and resistance to corruption.
Additionally, Excel 2007 introduced .xlsm and .xlsb file formats. The .xlsm format is similar to .xlsx, but it supports macros.
On the other hand, the .xlsb format is a binary format similar to .xls but comes with the benefits of the new OOXML features, making it more efficient for large data sets.
Now that you understand the history and differences between these file formats, you can make an informed decision on which one best suits your Excel needs.
Also read: Microsoft Excel vs. Google Sheets
Comparing XLS and XLSX Formats
When working with Microsoft Excel files, it’s important to understand the differences between the two primary file formats: XLS and XLSX.
In this section, we’ll compare these formats in terms of compatibility, file size and storage, and security.
Compatibility
If you need to share your Excel files with a diverse group of people, compatibility should be one of your main concerns.
The XLS format has higher compatibility, as it is readable by all Microsoft Excel versions, while XLSX is only readable by Excel 2007 and later versions. In other words, XLS files can be opened in older versions of Excel, ensuring that your work can be accessed by a wider range of users.
File Size and Storage
Another important difference between XLS and XLSX formats is the way they handle file size and storage.
XLSX files are generally smaller because it is an XML-based open file format. This means that the file size can be reduced through data compression, making it a more efficient option for storing large amounts of data.
On the other hand, XLS files are in a proprietary Microsoft format, which may result in larger file sizes.
Security
When it comes to security, XLSX files have an advantage over XLS files. The XLSX format does not support macros, which are small programs that can be embedded within a spreadsheet to automate tasks.
While macros can be useful, they can also pose a security risk if malicious code is embedded within them.
By not supporting macros, XLSX files can offer a safer environment for working with Excel data.
In summary, the choice between XLS and XLSX file formats depends on your specific needs.
For backward compatibility, you may choose to use XLS files, but for security and reduced file size, the XLSX format may be a better option.
Also read: CSV vs. XLSX Files – What’s the Difference?
Functionality and Features
In this section, we will explore the differences in functionality and features between XLS and XLSX file formats.
To better understand these differences, let’s divide them into three sub-categories:
Worksheets and Cells
Both XLS and XLSX files allow you to create worksheets with rows and columns. However, there are some limitations in the XLS format due to the older file architecture.
For instance, an XLS file can only support up to 65,536 rows and 256 columns, while an XLSX file can support up to 1,048,576 rows and 16,384 columns.
This higher capacity in XLSX files provides you with greater flexibility and increased ease of use when working with large amounts of data.
Formulas and Calculations
When it comes to formulas and calculations, both file formats offer similar tools and functions.
However, since XLSX is based on the Office Open XML format, it may provide better compatibility with newer Microsoft Excel functions that are not supported in the older XLS format.
If you need to use some of the latest Excel features, such as pivot tables, Power Query, and Power Pivot, it’s advisable to choose the XLSX format to ensure proper functioning and avoid compatibility issues.
Graphics and Charts
Creating graphics and charts in your Excel files is a great way to visually represent your data.
Both XLS and XLSX formats allow you to create various types of charts, but XLSX files offer more advanced options and better compatibility with the latest Excel features.
For example, using XLSX files will enable you to incorporate new chart types, such as waterfall and hierarchical charts.
When working with graphics and charts, you may find that using XLSX files provides a smoother experience and more customization options for your visual data representations.
In summary, while both XLS and XLSX file formats share many basic functionalities, the newer XLSX format provides better compatibility, storage capacity, and access to advanced features within Microsoft Excel.
Upgrading your files to the XLSX format might help you to better organize and analyze your data, but if you’re working with basic Excel tasks or with older versions of Excel, using the XLS format may be sufficient.
Also read: Excel vs Access
Excel Formulas and Macros
Excel is known for its ability to handle complex calculations and automate repetitive tasks.
In this section, we’ll discuss the support of formulas and macros in both XLS and XLSX file formats and the role of Visual Basic for Applications (VBA) in the process.
Visual Basic for Applications
Visual Basic for Applications (VBA) is the programming language used by Microsoft Excel to create macros.
Macros are a set of instructions that can automate tasks and perform complex calculations.
VBA allows you to streamline your work and save time by automating repetitive tasks in Excel.
XLSM: Excel Macro-Enabled Workbook
XLSM is a file format specifically designed to support Excel workbooks containing macros (VBA code).
While XLSX is the default file format for Excel 2007 and later versions, it does not support macros.
If you need to use macros in your spreadsheet, consider saving your workbook as an XLSM file.
An important difference between XLS and XLSX files is that XLS files can contain spreadsheets with or without macros, while XLSX files do not support macros.
If you’re working with an XLS file and need to include macros, you should convert it to an XLSM file as described in Microsoft Support.
In summary, both XLS and XLSX file formats are useful for working with Excel spreadsheets, but you need to keep in mind their support for formulas and macros.
XLS can handle spreadsheets with or without macros, while XLSX does not support macros.
To ensure your workbook is compatible with macros, you should use the XLSM format.
Also read: What is an XLK File?
Additional Excel File Formats and Extensions
When working with Excel, you may encounter various file formats beyond the popular XLS and XLSX extensions.
To better understand these formats, we’ll discuss additional file types including XLT, XLTX, XLTM, and ODS.
XLT
The XLT file format is an Excel Template file.
These templates provide predefined formats and layouts, saving you time when creating new workbooks with similar formatting requirements.
XLTX
Similar to XLT, the XLTX file format is an Excel Template used in newer Excel versions.
This format utilizes the XML-based open-file structure, providing improved compatibility with various operating systems and software applications.
XLTM
Another variant of the Excel Template file is the XLTM format.
It is specifically designed to store macros, which are custom scripts to automate tasks in Excel.
When you need to create a new Excel template workbook containing Visual Basic for Applications (VBA) code, you would save it as an XLTM file.
ODS
ODS stands for OpenDocument Spreadsheet, an open-source file format supported by many spreadsheet applications, including Microsoft Excel.
This format is especially useful for users who prefer open-source software or need to share files with people using different spreadsheet applications.
In summary:
- XLT and XLTX are Excel Template files that help you create pre-formatted workbooks easily.
- XLTM is a template format specifically for storing macros.
- ODS is an open-source file format that ensures compatibility with various spreadsheet applications.
When working in Excel, it’s important to choose the most suitable file format for your specific needs.
Understanding these additional file formats and their capabilities can help you work more efficiently and effectively with Excel.
Convert Between XLS and XLSX
Converting between XLS and XLSX formats is a simple process that can be done within Microsoft Excel itself.
To help you better understand the conversion process, follow these easy steps for each file type:
Convert XLSX to XLS:
- Open the XLSX file in Excel.
- Click on “File” in the top-left corner.
- Select “Save As” from the drop-down menu.
- Choose the location where you want to save the converted file.
- In the “Save as type” dropdown, select “Excel 97-2003 Workbook (*.xls)”.
- Click “Save” to complete the conversion process.
Convert XLS to XLSX:
- Open the XLS file in Excel.
- Click on “File” in the top-left corner.
- Select “Save As” from the drop-down menu.
- Choose the location where you want to save the converted file.
- In the “Save as type” dropdown, select “Excel Workbook (*.xlsx)”.
- Click “Save” to complete the conversion process.
Keep in mind that converting an XLS file that contains macros to XLSX format will remove those macros since XLSX files don’t support them.
If you need to keep the macros, you should save the file as an Excel Macro-Enabled Workbook (*.xlsm).
Now that you know how to convert between XLS and XLSX formats, you can easily take advantage of the features and benefits that each file type has to offer.
Compatibility with Other Software and Operating Systems
Google Sheets and Office Suites
XLS and XLSX files have different compatibility levels with various software programs and operating systems.
When using Google Sheets, a popular web-based spreadsheet application, both XLS and XLSX files can be opened and edited.
However, you might experience some formatting or feature differences in comparison to working in Microsoft Excel.
For alternative office suites like LibreOffice Calc and WPS Office Spreadsheets, you can open and work with XLS and XLSX files.
Yet, just like with Google Sheets, you might not have access to all the advanced features offered by Microsoft Excel.
To avoid any loss of data or functionality when working with these programs, you should use Excel-compatible formats when saving your work.
Excel for Mac
When it comes to compatibility with operating systems, Mac users can also work with both XLS and XLSX files using Excel for Mac.
However, there may be some differences in features and functionality between Excel for Windows and Excel for Mac.
For instance, Excel for Mac has a feature called “Compatibility Mode” for working with XLS files.
This mode allows you to use and save files in the older XLS format, but newer features in Excel will not be available, which helps prevent data loss or formatting issues when opening the file in older versions of the program.
In conclusion, both XLS and XLSX files can be opened and edited in various software programs and operating systems.
However, to ensure the best compatibility and maintain access to advanced features, it is preferable to work with Microsoft Excel on your preferred operating system.
XLS vs XLSX – Which is Better?
Understanding the differences between XLS and XLSX file formats is essential for effectively using Microsoft Excel in your daily tasks.
Let’s summarize the key points:
- XLS is an older, proprietary format compatible with all Microsoft Excel versions, while XLSX is a newer, XML-based open file format readable by Excel 2007 and later versions.
- XLS files can include macros, whereas XLSX files do not support macros.
- XLSX files tend to be smaller and less prone to corruption compared to XLS files. However, both file formats can be opened in newer versions of Excel for compatibility purposes.
When choosing between these two file formats, consider your specific needs and requirements. If you’re working with older Excel versions, XLS might be a better fit.
However, for more recent Office suites, XLSX offers better performance and reliability.
Remember, your choice of file format can impact the overall performance of your Excel files, particularly when dealing with large datasets.
So, always choose a format that best suits your needs and ensures compatibility with the intended audience.
Other Excel articles you may also like:
