Back
Excel

Compatibility Checker

Today we have the second guest post from Eric Patterson, Excel Program Manager.  Eric is writing about compatibility in Excel 2007.

As introduced in the last post, the Compatibility Checker scans your workbook for issues that are not supported by earlier versions of Excel.  When saving a file to the Excel 97-2003 format, Excel 2007 will scan the file and display the following dialog to help you make decisions about compatibility issues.

The dialog displays any issues found in your workbook and displays them in two categories that correspond to the impact they will have on the saved document.  The Compatibility Checker also includes these features:

  • The list of issues that are present in your workbook.
  • Summary of the number of occurrences of each issue.
  • Tools for locating the issues in your workbook and additional help for the types of issues.
  • An option to disable checking for compatibility when saving the workbook.
  • The ability to generate a report sheet in your workbook listing all of the issues found.

Compatibility Report

Clicking on the “Copy to New Sheet†button in the Compatibility Checker will insert a sheet in your workbook and create a report of all of the compatibility issues found, allowing you to work through each one to determine if you want to make any changes to the document before saving in the Excel 97-2003 file format.  Below is an example of a report sheet.

List of Compatibility Messages for Saving to Previous Versions

The Compatibility Checker scans for and displays warnings for a number of issues that can be found in your workbook.  Some of the warnings are for functionality or data that is removed from the workbook when it is saved, such as data outside of the row and column limits or formulas with more than 30 arguments.  Other types of issues affect how workbooks appear in previous versions of Excel, but nothing is lost when using Excel 2007 to view and edit the file.

Below I have a list of the messages that are displayed for the issues that are currently being scanned for.  These messages are not final and may change.

Worksheet and Formula Limits

  • “This workbook contains data in cells outside of the row and column limit of the selected file format. Data beyond 256 (IV) columns by 65,536 rows will not be saved. Formula references to data in this region will return a #REF! error.”
  • “This workbook contains dates in a calendar format that is not supported by the selected file format. These dates will be displayed as Gregorian dates.”
  • “This workbook contains dates in a calendar format that is not supported by the selected file format. These dates must be edited by using the Gregorian calendar.”
  • “Earlier versions of Excel do not support color formatting in header and footer text. The color formatting information will be displayed as plain text in earlier versions of Excel.”
  • “This workbook contains worksheets that have even page or first page headers and footers. These page headers and footers cannot be displayed in earlier versions of Excel.”
  • This workbook contains more unique cell formats than are supported by the selected file format. Some cell formats will not be saved.
  • “Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available.”
  • “This workbook contains more unique font formats than are supported in the selected file format. Some font formats will not be saved.”
  • “This workbook contains more cells with data than are supported in earlier versions of Excel. Earlier versions of Excel will not be able to open this workbook.”
  • “Some worksheets contain more array formulas that refer to other worksheets than are supported by the selected file format. Some of these array formulas will not be saved and will be converted to #VALUE! errors.”
  • “Some formulas contain more values, references, and/or names than are supported by the selected file format. These formulas will not be saved and will be converted to #VALUE! Errors.”
  • “Some formulas have more levels of nesting than are supported by the selected file format. Formulas with more than seven levels of nesting will not be saved and will be converted to #VALUE! errors.”
  • “Some formulas contain functions that have more arguments than are supported by the selected file format. Formulas that have more than 30 arguments per function will not be saved and will be converted to #VALUE! errors.”
  • “Some formulas use more operands than are allowed by the selected file format. These formulas will not be saved and will be converted to #VALUE! errors.”
  • “Some formulas contain functions with more arguments than are supported by the selected file format. Formulas with more than 29 arguments to a function will not be saved and will be converted to #VALUE! errors.”
  • “Some formulas contain references to tables that are not supported in the selected file format.  These references will be converted to cell references.”
  • “Some array formulas in this workbook refer to an entire column. In earlier versions of Excel, these formulas may be converted to #NUM! errors when they are recalculated.”
  • A label formula in this workbook will work correctly in earlier versions of Excel, and you will be able to see the entire formula. However, you will not be able to edit the formula.
  • “Some formulas in this workbook are linked to other workbooks that are closed. When these formulas are recalculated in earlier versions of Excel without opening the linked workbooks, characters beyond the 255-character limit cannot be returned.”
  • “One or more functions in this workbook are not available in earlier versions of Excel.  When recalculated in earlier versions, these functions will return a #NAME? error instead of their current results.”
  • “Some formulas contain references to tables in other workbooks that are not currently open in this instance of Excel.  These references will be converted to #REF on save to Excel 97-2003 format because they cannot be converted to sheet references.”

Conditional Formatting

“Some cells have more conditional formats than are supported by the selected file format. Only the first three conditions will be displayed in earlier versions of Excel.”

  • “Some cells have overlapping conditional formatting ranges. Earlier versions of Excel will not evaluate all of the conditional formatting rules on the overlapping cells. The overlapping cells will show different conditional formatting.”
  • “One or more cells in this workbook contain a conditional formatting type that is not supported in earlier versions of Excel, such as data bars, color scales, or icon sets.”
  • “Some cells contain conditional formatting with the ‘Stop if True’ option cleared. Earlier versions of Excel do not recognize this option and will stop after the first true condition.”
  • “Some cells contain ‘Top 10′ or ‘Compare to Average’ conditional formatting that does not calculate across all values. Earlier versions of Excel do not have this ability and will calculate the condition across all values.”
  • Some cells in this workbook contain conditional formatting with the Format entire row option selected. Earlier versions of Excel do not support this option and will not format the entire Table row.
  • “One or more cells in this workbook contain a conditional formatting type on a nonadjacent range (such as top/bottom N, top/bottom N%, above/below average, or above/below standard deviation). This conditional formatting type is not supported in earlier versions of Excel.”
  • “Some PivotTables in this workbook contain conditional formatting that may not function correctly in earlier versions of Excel. The conditional formatting rules will not display the same results when you use these PivotTables in earlier versions of Excel.”
  • Sorting and Filtering

    • “A worksheet in this workbook contains a sort state with more than three sort conditions. This information will be lost in earlier versions of Excel.”
    • “A worksheet in this workbook contains a sort state that uses a sort condition with a custom list. This information will be lost in earlier versions of Excel.”
    • “A worksheet in this workbook contains a sort state that uses a sort condition that specifies formatting information. This information will be lost in earlier versions of Excel.”
    • “Some data in this workbook is filtered in a way that is not supported in earlier versions of Excel. Rows that are hidden by the filter will remain hidden, but the filter itself will not display correctly in earlier versions of Excel.”
    • “Some data in this workbook is filtered by a cell color. Rows that are hidden by the filter will remain hidden, but the filter itself will not display correctly in earlier versions of Excel.”
    • “Some data in this workbook is filtered by a font color. Rows that are hidden by the filter will remain hidden, but the filter itself will not display correctly in earlier versions of Excel.”
    • “Some data in this workbook is filtered by more than two criteria. Rows that are hidden by the filter will remain hidden, but the filter itself will not display correctly in earlier versions of Excel.”
    • Some data in this workbook is filtered by a cell icon. Rows that are hidden by the filter will remain hidden, but the filter itself will not display correctly in earlier versions of Excel.
    • “Some data in this workbook is filtered by a grouped hierarchy of dates, resulting in more than two criteria. Rows that are hidden by the filter will remain hidden, but the filter itself will not display correctly in earlier versions of Excel.”

    PivotTables

    • “A PivotTable style is applied to a PivotTable in this workbook. PivotTable style formatting cannot be displayed in earlier versions of Excel.”
    • “A PivotTable in this workbook is built in the current file format and will not work in earlier versions of Excel. Only PivotTables that are created in Compatibility Mode will work in earlier versions of Excel.”
    • “A PivotTable in this workbook exceeds former limits and will be lost if it is saved to earlier file formats. Only PivotTables that are created in Compatibility Mode will work in earlier versions of Excel.”
    • “A PivotTable in this workbook has fields in compact form. In earlier versions of Excel, this layout will be changed to tabular form.”
    • “A PivotTable in this workbook contains conditional formatting rules that are applied to cells in collapsed rows or columns. To avoid losing these rules in earlier versions of Excel, expand those rows or columns.”

    Tables

    • “The table contains a custom formula or text in the total row. In earlier versions of Excel, the data is displayed without a table.”
    • “A table in this workbook does not display a header row. In earlier versions of Excel, the data is displayed without a table unless the Header Row check box is selected (Table Tools, Design tab, Table Style Options group).”
    • “A table style is applied to a table in this workbook. Table style formatting cannot be displayed in earlier versions of Excel.”
    • “A table in this workbook has a read-only connection to a Windows SharePoint Services List.  Table functionality will be lost, as well as the ability to refresh or edit the connection.  If table rows are hidden by a filter, they remain hidden in an earlier version of Excel.”

    Other Areas

    • “This workbook will be read-only and shared workbook features will not be available when someone opens it in an earlier version of Excel by using a file converter. To allow users to continue using the workbook as a shared workbook in earlier versions of Excel, you must save it in the file format of the earlier versions.”
    • “This workbook contains a customized Quick Access Toolbar and/or custom user interface parts that are not supported in earlier versions of Excel. These custom features will not be available in earlier versions of Excel.”
    • “Any effects on this object will be removed. Any text that overflows the boundaries of this graphic will appear clipped.”
    • “Uninitialized ActiveX controls cannot be transferred to the selected file format.  The controls will be lost if you continue.”

    Next Time

    Over the next several posts I’ll be providing more in-depth information about the features that are reported by the compatibility checker and describe the experience of working with these features in Excel 2007 and previous versions.

    Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\taxonomy.php on line 3255 Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\category-template.php on line 1245

    Tags