You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Guest writer: Eric Patterson. Over the next several posts, Eric, one of the members of the Excel Program Management team, is going to discuss backwards compatibility in Excel 2007. I hope readers find this interesting.
Over the next several posts I will describe the features that we have built-in to Excel 2007 to make it easy to migrate from previous versions.
If you’ve been reading along with this blog, you should have a good picture of the scope of the changes that we are making in this release. It has been a number of years since we changed the default file format for Excel and during that time we have learned a lot of lessons about how people migrate to new versions of our software. For more information about the new file formats, see All about File Formats.
Our goal is to minimize the amount of “disruption” people experience sharing workbooks with each other using different versions of Excel.
Compatibility Mode is a setting for each workbook designed to prevent incompatible features in Excel 2007 from being saved to a file destined for use with a prior version of Excel.
For example, versions of Excel prior to Excel 2007 can support at most 65,536 rows in a worksheet. If an Excel 2007 user creates a worksheet with 100,000 rows of data, prior versions of Excel must truncate rows beyond 65,536 when opening the file. To minimize disruption to users when collaborating between versions of Excel, incompatible features in Excel 2007 are disabled while Excel 2007 is running in compatibility mode.
When is Compatibility Mode “On”?
To simplify how people work with workbooks, we have tied Compatibility Mode to the Excel 97-2003 file format. Whenever a file in this format is opened, the workbook will be in Compatibility Mode. Also, when you save a new file to the Excel 97-2003 format, the next time it is opened the workbook will be in Compatibility Mode.
To exit Compatibility Mode for the workbook you save the file to one of the new file formats and re-open the workbook. This can be accomplished in one step using the Convert command.
Features Disabled in Compatibility Mode
One of the ways that we enable people to create documents that are compatible with previous versions is by disabling architectural changes that we are not able to save to previous versions of our file format. For these features, the functionality works as it did in Excel 2003:
Compatibility of Other Features
The second way that we help people to create compatible documents is with the Compatibility Checker. The Compatibility Checker scans your workbook for compatibility issues and provides information about them. When in Compatibility Mode, the Compatibility Checker will run whenever a workbook is saved into a previous version file format. The next blog post will provide detailed information about the compatibility checker.
Thanks for the info. I think there is a confusion that you may want to tear apart. It's the difference between a previous version of Excel not supporting a feature that only came after, and the round-tripping scenario which allows a Excel 2007 file to be saved back as an Excel 97-2003 file, updated there, then reopened in Excel 2007 later with all Excel 2007 features kept in place.
Let me give you one example : databars. Databars are stored in BIFF8 when you store the file downlevel. Anyone willing to check this out can add a databar, save it as Excel 97-2003, open the file there, make changes, save, then open again the file in Excel 2007 : big surprise, the databar is still there.
Now for the interesting part : rows after the 65536-th : why a different behavior? why can you save a databar in new BIFF8 records, and not new labels or formulas? This makes no sense. Case in point : create a worksheet with a label in row 65537. Save it as Excel 97-2003 (the compatibility dialog needs to be agreed). Open it there (the label does not appear), save. Reopen it in Excel 2007 : the label is gone. Bad.
So either the compatibility dialog needs to appear always (including in the databar case) or never.
Let me say it differently, it's a perfect logic to only show the compatibility dialog if some loss is going to happen (like rows after the 65536-th). The problem is, users don't understand that, why should they? They won't make the difference between adding a databar and adding a label in row 65537. So this creates an inconsistency for them (although it's the right logic from an implementation perspective since there is a real loss in only one case above). This sets yourself for a lot of unnecessary customer support IMHO.
I gather this means that the new file format isn't compatible with the "millions of billions" of existing excel documents after all.
Eric, I look forward to the ensuing discussion.
From the experienced user point of view, the natural tendency would be to avoid features that are not compatible if working in a mixed environment. I've encountered one instance where compatibility cannot be avoided, and as luck would have it, it's turning out to be a major headache (as I mentioned in a mail to Dave some time ago.)
The problem involves database queries. There's no option in Excel 2007 to import into a QueryTable. The options are Table and PivotTable. When you save the file in the older format, there are formula referencing and name scoping issues that must be handled, which can be an annoyance at the very worst. A more significant issue is that after opening the file in Excel 2003, you can no longer edit the query. Excel 2003 complains that “The external data range was created programmatically and cannot be edited.” I get error messages even attempting to refresh the query in Excel 2003. Admittedly, these may be Beta 2 issues that have been rectified in the current builds.
The point is; It would be better (not to mention safer) if one didn't have to deal with "forced" compatibility issues, which, in this case, could have been avoided completely if there was an option in Excel 2007 to import into an old-fashioned QueryTable. There’s probably a good reason for this omission, although the logic escapes me.
Thanks for the feedback everyone.
Stephane - We are trying to differentiate between features that are lost on save and those that don't display in previous versions, but are retained. We are keeping everything that our previous file format can support, but some of the architectural items, such as more rows and columns cannot be supported, hence the file format change for this version.
requiredName - Excel 2007 will read and write Previous version Excel files.
Colin - I will look into the QueryTable issue based on the information that you have already provided.