Back
Excel

Compatibility Mode

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

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:

  • The total number of available columns in Excel – Restricted to 256 (New Limit: 16k)
  • The total number of available rows in Excel – Restricted to 64k  (New Limit: 1M)
  • Max number of arguments per function – Restricted to 30 (New Limit: 255)
  • Number of levels of nesting in formulas – Restricted to 7  (New Limit: 64)
  • Formula Length – Restricted to 1k characters (New Limit: 8k characters)
  • Structured Referencing of Tables in Formulas – Structured References to tables are not created automatically when formulas are created.  Structured References are saved as cell reference in previous version file formats.
  • PivotTable Features – PivotTables created in compatibility mode are created as “Version 11†PivotTables.  A number of New PivotTable Features are disabled or changes for compatibility with previous versions of Excel.  A subsequent will describe these features in more detail.

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.