Ensuring Compatibility with Older Excel Versions

In Mary's company they often send Excel workbooks to vendors and contractors, all using differing versions of Excel. She wonders if there is a way to ensure that they don't inadvertently use a worksheet function that will not work on older versions of Excel that others may be using. Mary notes that in their office they don't have all the older versions of the program in order to test.

In the old days (not really that long ago), Microsoft would update Excel every couple of years and introduce a limited number of new features in each version. One might reasonably refer to these as the "good old days," because now it seems that Microsoft is introducing new features every couple of months. And some of those new features can be showstoppers, if used and shared with people using older versions of the software.

Fortunately, Excel includes a compatibility checker that can help you identify whether your workbook uses features that could cause heartburn for users of older versions. To use the checker, follow these steps:

1. Save your workbook as you normally would.
2. Display the File tab of the ribbon.
3. At the left side of the screen, click Info. Excel displays information about the workbook and some tools.
4. Click the Check for Issues button. Excel displays a drop-down list of things you can check.
5. Click the Check Compatibility option. Excel displays the Compatibility Checker dialog box. (See Figure 1.)



Figure 1. The Compatibility Checker dialog box.

At this point you can see what issues are flagged by Excel. You can filter which issues are displayed by using the Select Versions to Show drop-down list. You can also tell Excel to run the Compatibility Checker each time the workbook is saved by clicking the appropriate checkbox near the bottom of the dialog box. Also, if you click the Copy to New Sheet button, then Excel allows you to copy the compatibility information to a new worksheet so you can use it as a checklist to track down the compatibility issues.

If you have people who are using even older versions (pre-2007), the best approach is to simply do a Save As (press F12) and save in the .XLS format. During the process of saving, Excel will let you know what features will not be saved into the older file format.

You can find additional information by accessing either of these pages on Microsoft's support site:

https://go.tips.net/compat
https://go.tips.net/compat2

Of course, you may want to consider if the vendors and contractors really need access to the workbook in Excel format. If all they need to do is to see, for example, is an inventory level, a project status, or a price list, then consider generating a PDF file from your workbook. That PDF can be viewed by just about anyone without the need for you to be concerned about Excel version compatibility.

Finally, if you simply need to share raw data (values) with your contractors or vendors, then you might consider saving your worksheet in CSV format. Then the data can be loaded into whatever spreadsheet program they are using. It won't have any formatting, but it will allow you to share data rather easily.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9974) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.