The file extension in your workbook's name should be .xlsx if you're using an Excel version post-Excel 2003. That’s not necessarily the case with everyone as they may be using older Excel versions with the .xls file extension.
While your later version would be all accepting of .xls files, others' younger Excel would be unaware of certain features of .xlsx files but you can give the new features some rest for usage in older versions – through Compatibility Mode. Compatibility sounds like it's a good thing and now you want to find out what it is.
We’re answering some questions you may have surrounding Compatibility Mode in Excel. What is Compatibility Mode with regard to Excel? What should I know? How do I run Compatibility Mode? What do I do if I’m ready to change my file back to the latest format? What differences have a major impact on the file? How can I pinpoint the incompatible features? The good part is you don’t have to; Excel will do it for you.
What is Compatibility Mode in Excel?
Compatibility Mode in Excel facilitates working with older file formats and helps with checking features in the current version that are not supported by earlier Excel versions. Excel, like anything else out there, is subject to evolving, changing, and updating as it gets topped up with new features.
Compatibility Mode disables aspects of incompatible features that older versions would try to perform or calculate and would otherwise malfunction.
When are you likely to see Compatibility Mode in Excel? If you open an old file or receive an xls file and your Excel is version 2007 or later, you will see the Compatibility Mode in the title bar. You will also see this appear in the title bar if you’ve converted the file format to an older one.
Compatibility Issues
The number one reason a feature or function may not work in older versions as we'd like it to is that it hadn't existed in the previous versions. There are several new functions that are introduced with each Excel version and over time, many data styling features have made themselves available with the later updates in Excel.
E.g. if a newer function is recalculated in an earlier version of Excel, it will return a #NAME? error since the version will not recognize the function and will deem the function name incorrect.
However, compatibility issues aren’t just linear like that; there may be version incompatibility among xlsx files too. E.g. the UNICODE function will not work in Excel 2010 although, after 2003, xlsx files have replaced xls. But the function was introduced in 2013 so it won't work in any version before that, xlsx or not.
While Compatibility Mode works nicely to tone down the file against alien features, there is a possibility that disabling that feature is causing a major lack of functionality. That’s the bad news. The good news is that Excel’s Compatibility Checker can synopsize the compatibility issues, categorized into major and minor features.
Minor issues are more superficial and concern mostly with appearance and minor functionality. Major issues may cause significant loss in functionality or even data loss. Once presented by the Compatibility Checker you may choose to address these issues.
How to Save a Spreadsheet in Compatibility Mode
Saving a file in Compatibility Mode assists smooth usage of the file over other Excel versions. This is particularly helpful if the file is passed to other users and computers that do not share the same Excel version. The steps to save a spreadsheet in Compatibility Mode are given below:
This is what the xlsx file should look like before applying the Compatibility Mode. The title bar only carries the name of the file.
- Select the File tab in the top left corner.
- From the leftward pane, select Save As.
- Click on the arrow of the bar with the file type to open a drop-down list.
- Select the file type that you want to make the current version compatible with e.g. we’ve select xls.
- You can edit the file name and location before hitting the Save
- A separate file with the given name and format will be saved to the provided location. Now whenever the file will be opened, the title bar will show that the file is running with the Compatibility Mode.
- If your file contains any incompatible features, you will automatically be notified of them via the Compatibility Checker when you hit the Save
- Close the Compatibility Checker to continue with the file by selecting the Continue
Cancel the Compatibility Mode and creation of a separate file type with the Cancel command or the Close button. In both these cases, a Save As dialog box will open asking how you want to save the file. At this point, you may choose to save the file in a newer file format to retain some functionality.
As mentioned, the appearance of the Compatibility Checker here is automatic and serves as a notification. You can access the Compatibility Checker at any time and you find how to do that below.
How to Check Compatibility Manually
The compatibility of the file can be checked manually and it is done using the Compatibility Checker. The Compatibility Checker in Excel creates a summary of the features that will not be available or function properly if the current workbook is saved in earlier file formats or Excel versions.
Among other things, the Compatibility Checker enlists a description of the incompatible feature, the sheet location, and versions that the file will not be compatible with.
Let’s advance on how to access the Compatibility Checker and what we can find through it.
- From the File tab, click on Info from the side panel.
- In the Info window, click on the Check for Issues button and select Check Compatibility from the list.
- The window will jump back to the worksheet and open the Compatibility Checker. Now you can see all the features that are likely to malfunction in older versions of Excel.
The summary is split into Significant loss of functionality and Minor loss of fidelity. Each feature is described with the nature, location, occurrences in the workbook and the Excel versions it will clash with. To only enlist incompatibilities with selective versions, click on the Select versions to show button and unmark the versions that you don’t want checked.
Marking the Check compatibility when saving this workbook box will have the Compatibility Checker pop-up every instance the workbook is saved.
The Help link opens to the Microsoft website regarding information on how the feature poses an issue. The Fix link may appear with some features and clicking on this link will fix the compatibility issue and remove the feature from the Compatibility Checker.
The Find link will lead you to the sheet and select the cells containing the feature. But this will close the Compatibility Checker every time and you will have to reopen it from the Info window. You can lodge the summary on another worksheet to avoid this hassle and access the ranges from there by clicking on the Copy to New Sheet command.
How to Get Out of Compatibility Mode
There are quite a few ways of kicking a file out of Compatibility Mode which may be required temporarily or permanently for the smoother application and utilization of the newer features and functions in Excel.
Leaving the Compatibility Mode involves changing the file type to xlsx Excel workbook. Therefore, any of the Save As routes will do the trick where you change the file type to xlsx (and not just the file extension, mind that!). Below are a couple of ways to save the file as the xlsx file type and exit Compatibility Mode.
Save As Option
- Go to File tab > Save As.
- Select Excel Workbook (*xlsx) from the file types in the drop list and hit the Save
This will create a separate file in the xlsx format, Compatibility Mode gone!
Convert Option
- Head to File tab > Info
- Select the Convert button with the Compatibility Mode
- You may see this confirmation pop-up:
- You will be led out of the Info window as a Save As dialog box appears, already with the xlsx file type selected for saving.
- Click on the Save command after adjusting the name and location of the file if need be.
There may be another dialog box confirming the conversion and if you want to reopen the workbook right away to activate the newer features of the xlsx file.
Are the teachings of this tutorial compatible with your understanding of the Compatibility Mode? We’re counting on it! Because together, we’re going to conquer the Excel world with all the hacks and tricks we can muster and we’ve got to keep up to track, things are constantly spinning on the Excel globe. Let’s catch up for discovering and exploring a new avenue!
About Mehwish Javaid