Compatibility Alert: This Excel Formula Won’t Work in Older Versions

Have you ever encountered the error message “this formula is not supported by some older versions of Excel” when trying to use certain formulas or functions in Microsoft Excel? This can be a frustrating issue, especially if you need to share your spreadsheets with colleagues or clients who may be using older versions of the software. In this article, we’ll explain what causes this error message and provide solutions for working around it.

What Causes the “This Formula Is Not Supported” Error in Excel?

The main reason you see the “this formula is not supported by some older versions of Excel” error is because the formula or function you are trying to use was introduced in a newer version of Excel than the one your spreadsheet is saved in. For example:

• The IFS function was introduced in Excel 2016
• The MAXIFS and MINIFS functions were introduced in Excel 2019
• Dynamic array formulas like FILTER, SORT, UNIQUE and SORTBY were introduced in Excel 365

If you try to use any of these functions in an Excel file saved in the .xls format (used by Excel 97-2003), or even the newer .xlsx format but saved to be compatible with earlier versions like Excel 2007 or 2010, you will see the error.

Excel Version Compatibility Table

Here is a quick reference table showing which versions of Excel support different formula and function capabilities:

As you can see, using the latest formulas requires having one of the newer versions of Excel like 2019 or 365. Files shared with users on older versions will show the compatibility error for unsupported formulas.

Also, here is a compatibility chart showing which versions can open files from other versions:

Why Doesn’t Microsoft Make New Excel Versions Backwards Compatible?

You may be wondering – why doesn’t Microsoft just make new versions of Excel backwards compatible with files created in older versions to avoid this whole issue? The answer comes down to the technical limitations and performance trade-offs involved.

Supporting an ever-expanding set of legacy features and file formats takes considerable development resources, weighs down the application size, and can hurt performance for users working primarily with newer features. At a certain point, Microsoft has to make a decision to optimize for the current and future needs of most users, which may come at the expense of those clinging to much older versions.

Additionally, each new version of Excel has a larger grid size, higher limits on colors and conditional formats, and various other specification changes under the hood. Ensuring full backwards compatibility with files taking advantage of these expanded limits would be nearly impossible.

That said, Microsoft does provide a compatibility mode option to save workbooks in a format that can be opened in earlier versions of Excel. However, this mode disables functionality that isn’t supported by the earlier version, which can result in lost data or formatting for complex workbooks. It’s generally better to save in the default .xlsx format.

Solutions for Fixing the “This Formula Is Not Supported” Issue

So what can you do if you need to use a formula that isn’t supported in the version of Excel you’re working with or sharing to? Here are a few options:

1. Upgrade to a Newer Version of Excel

The most straightforward solution is to upgrade to a more recent version of Excel that includes support for the formulas you need. For most users, this means upgrading to Microsoft 365, which is the subscription-based version of Office that provides access to the latest features and updates.

If upgrading isn’t an option, you can still work around the issue with some alternative approaches.

2. Use Alternative Formulas or Functions

In many cases, you can achieve the same result as a newer unsupported formula by using a combination of older, more widely supported functions.

For example, instead of using IFS, you can nest multiple IF statements:

=IF(A1>10, ">10", IF(A1>5, ">5", "<=5"))

And instead of MAXIFS/MINIFS, you can use an array formula like:

=MAX(IF(A1:A10>10, B1:B10))

These alternative formulas are more verbose, but will provide compatibility with older Excel versions.

Another option is to use VBA macros to replicate the functionality of newer built-in functions. While this requires more technical expertise, it can be a viable solution for automating complex calculations in a backwards-compatible way.

3. Save Workbooks in the .xlsx Format

If you’re not using any formulas introduced after Excel 2007, you can avoid issues with older versions by saving your workbooks in the .xlsx format instead of the legacy .xls format.

The .xlsx format has been the default since Excel 2007 and supports a much larger grid size, more colors, better security, and other improvements. Saving in this format ensures your files can be opened in Excel 2007 and later.

Be aware that password-protected .xlsx files cannot be opened in Excel 2003 or earlier at all. If you need to secure your workbooks and support these legacy versions, you’ll have to use the older .xls format.

4. Use External References for Complex Formulas

If you have certain complex calculations that require newer formulas, consider moving those formulas to a separate “calculator” spreadsheet saved in the .xlsx format. You can then use cell references to link the results back into your main workbook that is saved in a more compatible format.

This allows you to take advantage of the latest functions without causing issues for colleagues using older versions. They simply won’t be able to edit the formulas in the external reference sheet.

You can also use the GETPIVOTDATA function to retrieve data from pivot tables in another workbook without actually linking the workbooks together. This can be a good solution if you need to use complex pivot table features not supported in older Excel versions.

Tips for Avoiding Excel Version Compatibility Issues

To minimize running into “this formula is not supported” errors in the future, keep these tips in mind:

• Communicate with your team about which versions of Excel are in use and what the minimum supported version is for shared workbooks
• Be cautious about quickly adopting the latest formulas and functions if you collaborate with others who may not have the newest Excel version
• Save files in the .xlsx format whenever possible for better compatibility and features compared to .xls
• Consider using Google Sheets for cloud-based collaboration, as it supports many of the same formulas as Excel and doesn’t have the same issues with version compatibility
• If you maintain spreadsheets used by many others, include documentation about the minimum supported Excel version required to avoid confusion
• Avoid using whole column or whole row references (like A:A) in your formulas, as these can significantly slow down calculation in large worksheets, especially in older versions of Excel
• Use named ranges instead of direct cell references whenever possible to make your formulas more readable and maintainable

Keyboard Shortcuts for Checking Excel Version

Here are a couple handy keyboard shortcuts for checking which version of Excel you’re currently using:

• Press Alt+F11 to open the Microsoft Visual Basic for Applications window, then look at the title bar
• Press Ctrl+Shift+Esc to open Task Manager, find “Excel.exe” under the Details tab, and look at the version number next to the process

With these shortcuts, you can quickly identify your Excel version without having to dig through menus or settings screens.

Final Thoughts

Encountering a “this formula is not supported by some older versions of Excel” error can disrupt your productivity in Excel, but it doesn’t have to be a major roadblock. By understanding the cause of the error and using alternative approaches like modified formulas, saving in newer file formats, or separating complex calculations with external references, you can maintain compatibility when collaborating with others using different versions.

Awareness of what version of Excel you’re using and clear communication with your team can also help avoid these issues in the future. With a bit of planning and flexibility, you can take advantage of powerful new Excel features while still sharing workbooks seamlessly with colleagues on older versions.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.