Office Excel 2007 features that are not supported in earlier versions of Excel

Not all Microsoft Office Excel 2007 features are supported in earlier versions of Excel. When you work in compatibility mode or want to save an Excel 2007 workbook to the file format of an earlier version of Excel, the Compatibility Checker can help you identify issues that may cause a significant loss of functionality or a minor loss of fidelity in the earlier version of Excel. To avoid the loss of data or functionality in an earlier version of Excel, you can then make the necessary changes to your Excel 2007 workbook.

The Compatibility Checker lists the issues it finds, and provides a Find and Help button for most issues, so that you can locate all occurrences of the issue, and get information on best ways to solve the issue.

 Note   When you click Find in the Compatibility Checker, Excel typically selects only cells and ranges that are affected by compatibility issues such as cell formatting inconsistencies, incompatible values in cells, or cells and ranges that contain data outside the row and column limit of earlier versions of Excel. However, when specific features such as grouping have been applied to cells that are affected by compatibility issues, Excel selects the entire worksheet range containing the grouped cells that are located outside the row and column limit of earlier versions of Excel instead of selecting just those grouped cells.

In this article


Worksheet issues

The following worksheet issues cause a significant loss of functionality:

Issue Solution
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.

In Excel 2007, the worksheet size is 16,384 columns by 1,048,576 rows, but the worksheet size of earlier versions of Excel is only 256 columns by 65,536 rows. Data in cells outside of this column and row limit is lost in earlier versions of Excel.

In the Compatibility Checker, click Find to locate the cells and ranges that fall outside of the row and column limits, select them, and then place them within the column and row limits or on another sheet by using the Cut and Paste commands.

For more information, see:

Move or copy cells and cell contents

Move or copy rows and columns

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.

In Excel 2007, you can create custom international calendar formats, such as Hebrew Lunar, Japanese Lunar, Chinese Lunar, Saka, Zodiac Chinese, Zodiac Korean, Rokuyou Lunar, and Korean Lunar. However, these calendar formats are not supported in earlier versions of Excel.

To avoid loss of functionality, you may want to change the calendar format to a language (or locale) that is supported in earlier versions of Excel.

For more information, see:

Display numbers as dates or times

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.

In Excel 2007, you can apply a non-Western calendar type, such as Thai Buddhist or Arabic Hijri. In earlier versions of Excel, these calendar types can only be edited in Gregorian.

To avoid loss of functionality, you may want to change the calendar format to a language (or locale) that is supported in earlier versions of Excel.

For more information, see:

Display numbers as dates or times

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.

In Excel 2007, the total number of available cell blocks (CLBs) is limited by available memory. In earlier versions of Excel, however, the total number of available CLBs is limited to 64,000 CLBs in an instance of Excel.

A CLB includes 16 worksheet rows. If all rows in a worksheet contain data, you would have 4096 CLBs in that worksheet, and you could have only 16 of such worksheets in a single instance of Excel (regardless of how many workbooks you have open in Excel).

To ensure that the workbook does not exceed the 64,000 CLB limit and can be opened in earlier versions of Excel, you should work in Compatibility Mode in Excel 2007 after you save the workbook to Excel 97-2003 file format. In Compatibility Mode, Excel keeps track of the CLBs within the active workbook.

For more information, see:

Move or copy cells and cell contents

Move or copy rows and columns

Use Office Excel 2007 with earlier versions of Excel


The following worksheet issues cause a minor loss of fidelity:

Issue Solution
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.

In Excel 2007, you can apply color formatting to header and footer text. You cannot use color formatting in headers and footers in earlier versions of Excel.

In the Compatibility Checker, click Fix if you want to remove the color formatting.

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.

In Excel 2007, you have the option to display different header and footer text on even pages or on the first page. This option is not available in earlier versions of Excel. Even page or first page headers and footers cannot be displayed in the earlier versions, but they remain available for display when you open the workbook in Excel 2007 again.

If you frequently need to open a workbook in an earlier Excel file format, you may want to stop using even or first page headers or footers for that workbook.

For more information, see:

Add or change page headers and footers

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.

In Excel 2007, different cell formatting or cell style options are available, such as special effects and shadows. These options are not available in earlier versions of Excel.

You can accept the closest available format that is applied when you continue saving the workbook, or you can change or remove a cell style that is not supported before you save the workbook in an earlier Excel file format.

For more information, see:

Apply, create, or remove a cell style

This workbook contains more unique cell formats than are supported by the selected file format. Some cell formats will not be saved.

In Excel 2007, you can use 64,000 unique cell formats, but in earlier versions of Excel, you can only use up to 4,000 unique cell formats. Unique cell formats include any specific combination of formatting that is applied in a workbook.

For more information, see:

Apply, create, or remove a cell style

This workbook contains more unique font formats than are supported in the selected file format. Some font formats will not be saved.

In Excel 2007, 1,024 global font types are available, and you can use up to 512 of them per workbook.

For more information, see:

Change the font or font size in Excel


Top of Page Top of Page

Excel table issues

The following Excel table issues cause a minor loss of fidelity:

Issue Solution
The table contains a custom formula or text in the total row. In earlier versions of Excel, the data is displayed without a table.

In Excel 2007, you can use custom formulas and text in the total row of a table, but this is not supported in earlier versions of Excel. Although the formulas will remain, the range will no longer be a table. If you want the table to remain in earlier versions of Excel, you may want to remove the custom text and use only the formulas that are available in the total row.

In the Compatibility Checker, click Find to locate the table that contains a custom formula or text, and then remove that formula or text from the total row.

For more information, see:

Total the data in an Excel 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).

In Excel 2007, you have the option of displaying or hiding the header row of a table. In earlier versions of Excel, a table always has a header row.

In the Compatibility Checker, click Find to locate the table that is displayed without a header row, and then display a header row.

For more information, see:

Turn Excel table headers on or off

A table style is applied to a table in this workbook. Table style formatting cannot be displayed in earlier versions of Excel.

In Excel 2007, you can quickly format a table by applying a theme-based table style. In earlier versions of Excel, you can only format a table manually.

In the Compatibility Checker, click Find to locate the table that has a table style applied, and then remove that table style.

For more information, see:

Format an Excel table

A table in this workbook is connected to an external data source. Table functionality will be lost, but the data remains connected. If table rows are hidden by a filter, they remain hidden in an earlier version of Excel.

To avoid losing table functionality in earlier versions of Excel, you may want to disconnect the external data source before you save the workbook in an earlier Excel file format.

In the Compatibility Checker, click Find to locate the table that is connected to an external data source, and then disconnect the table from the external data source. In the earlier version of Excel, you can then connect the data to the external data source.

For more information, see:

Use Microsoft Query to retrieve external data

Create, edit, and manage connections to external data

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.

To avoid losing table functionality in earlier versions of Excel, you may want to disconnect the table from the Windows SharePoint Services list before you save the workbook in an earlier Excel file format.

You can also decide to create a SharePoint list that is read/write only in the earlier version of Excel, and then work on this workbook in Compatibility Mode in Excel 2007, which keeps it in Excel 97-2003 file format.

In the Compatibility Checker, click Find to locate the table that has a read-only connection to a Windows SharePoint Services List, and then disconnect the table from the SharePoint List. In Excel 2003, you can then import the SharePoint List as a read/write list.

For more information, see:

Use Office Excel 2007 with earlier versions of Excel


Top of Page Top of Page

PivotTable issues

The following PivotTable issues cause a significant loss of functionality:

Issue Solution
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.

In Excel 2007, a PivotTable report supports 1,048,576 unique items per field, but in earlier versions of Excel, only 32,500 items per field are supported.

In the Compatibility Checker, click Find to locate the PivotTable that exceeds the former limits. When you save the workbook to Excel 97-2003 format, you can then re-create this PivotTable in Compatibility Mode.

For more information, see:

Use Office Excel 2007 with earlier versions of Excel

Working with different PivotTable formats in Office Excel

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.

To avoid losing conditional formatting rules that are applied to cells in collapsed rows or column, you should expand the rows and columns before you save the workbook in an earlier Excel file format.

In the Compatibility Checker, click Find to locate the collapsed rows or columns that contain conditional formatting rules, and then expand those rows or columns.

For more information, see:

Add, change, or clear conditional formats


The following PivotTable issues cause a minor loss of fidelity:

Issue Solution
A PivotTable style is applied to a PivotTable in this workbook. PivotTable style formatting cannot be displayed in earlier versions of Excel.

In Excel 2007, you can quickly format a PivotTable report by applying a theme-based PivotTable style. In earlier versions of Excel, you can only format a PivotTable manually.

In the Compatibility Checker, click Find to locate the PivotTable that has a PivotTable style applied, and then remove that PivotTable style.

For more information, see:

Working with different PivotTable formats in Office 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 that has been created in the Excel 2007 file format cannot be refreshed in earlier versions of Excel. To avoid this issue, you may want to work in Compatibility Mode. When you create a PivotTable in Compatibility Mode, you can open it in earlier versions of Excel without loss of functionality.

In the Compatibility Checker, click Find to locate the PivotTable that is created in the current file format. When you save the workbook to Excel 97-2003 format, you can then re-create this PivotTable in Compatibility Mode.

For more information, see:

Use Office Excel 2007 with earlier versions of Excel

Working with different PivotTable formats in Office Excel

A PivotTable in this workbook has fields in compact form. In earlier versions of Excel, this layout will be changed to tabular form.

In Excel 2007, you can use compact form (alone or in combination with tabular and outline form) to keep related data from spreading horizontally off of the screen and to help minimize scrolling — fields are contained in one column and are indented to show the nested column relationship. In earlier versions of Excel, only outline form and tabular form are supported.

In the Compatibility Checker, click Find to locate the PivotTable that has fields in a compact form, and then change that format to tabular form as needed.

For more information, see:

Working with different PivotTable formats in Office Excel

Design the layout and format of a PivotTable report


Top of Page Top of Page

Sorting and filtering issues

The following sorting issues cause a minor loss of fidelity:

Issue Solution
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.

In Excel 2007, you can apply sort states with up to sixty-four sort conditions to sort data by, but earlier versions of Excel support sort states with up to three conditions only. To avoid losing sort state information in earlier versions of Excel, you may want to change the sort state to one that uses no more than three conditions. In earlier versions of Excel, users can also sort the data manually.

All sort state information remains available in the workbook, however, and is applied when the workbook is opened again in Excel 2007, unless the sort state information is edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate the data that has been sorted with more than three conditions, and then change the sort state by using only three or less conditions.

For more information, see:

Sort data in a range or table

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.

In Excel 2007, you can sort by a custom list. To get similar sorting results in earlier versions of Excel, you can group the the data that you want to sort, and then sort the data manually.

All sort state information remains available in the workbook, however, and is applied when the workbook is opened again in Excel 2007, unless the sort state information is edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate the data that has been sorted by a custom list, and then change the sort state so that it no longer contains a custom list.

For more information, see:

Sort data in a range or table

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.

In Excel 2007, you can sort data by a specific format, such as cell color, font color, or icon sets. In earlier versions of Excel, you can only sort text.

All sort state information remains available in the workbook, however, and is applied when the workbook is opened again in Excel 2007, unless the sort state information is edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate the data that has been sorted by a specific format, and then change the sort state without specifying formatting information.

For more information, see:

Sort data in a range or table


The following filtering issues cause a minor loss of fidelity:

Issue Solution
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.

In Excel 2007, you can apply filters that are not supported in earlier versions of Excel. To avoid losing filter functionality, you may want to clear the filter before you save the workbook in an earlier Excel file format. In earlier versions of Excel, users can then filter the data manually.

All filter state information remains available in the workbook, however, and is applied when the workbook is opened again in Excel 2007, unless the filter state information is edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate the data that has been filtered, and then you can clear the filter to unhide the rows that are hidden. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter.

For more information, see:

Filter data in a range or table

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.

In Excel 2007, you can filter by a cell color, font color, or icon set, which is not supported in earlier versions of Excel. To avoid losing filter functionality, you may want to clear the filter before you save the workbook in an earlier Excel file format. In earlier versions of Excel, users can then filter the data manually.

All filter state information remains available in the workbook, however, and is applied when the workbook is opened again in Excel 2007, unless the filter state information is edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate the data that has been filtered, and then you can clear the filter to unhide the rows that are hidden. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter.

For more information, see:

Filter data in a range or table

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.

In Excel 2007, you can filter by a cell color, font color, or icon set, which is not supported in earlier versions of Excel. To avoid losing filter functionality, you may want to clear the filter before you save the workbook in an earlier Excel file format. In earlier versions of Excel, users can then filter the data manually.

All filter state information remains available in the workbook, however, and is applied when the workbook is opened again in Excel 2007, unless the filter state information is edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate the data that has been filtered, and then you can clear the filter to unhide the rows that are hidden. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter.

For more information, see:

Filter data in a range or table

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.

In Excel 2007, you can filter by a cell color, font color, or icon set, which is not supported in earlier versions of Excel. To avoid losing filter functionality, you may want to clear the filter before you save the workbook in an earlier Excel file format. In earlier versions of Excel, users can then filter the data manually.

All filter state information remains available in the workbook, however, and is applied when the workbook is opened again in Excel 2007, unless the filter state information is edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate the data that has been filtered, and then you can clear the filter to unhide the rows that are hidden. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter.

For more information, see:

Filter data in a range or table

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.

In Excel 2007, you can filter data by more than two criteria. To avoid losing filter functionality, you may want to clear the filter before you save the workbook in an earlier Excel file format. In earlier versions of Excel, users can then filter the data manually.

All filter state information remains available in the workbook, however, and is applied when the workbook is opened again in Excel 2007, unless the filter state information is edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate the data that has been filtered, and then you can clear the filter to unhide the rows that are hidden. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter.

For more information, see:

Filter data in a range or table

Filter by using advanced criteria

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.

In Excel 2007, you can filter dates by a grouped hierarchy. Because this is not supported in earlier versions of Excel, you may want to ungroup the hierarchy of dates. To avoid losing filter functionality, you may want to clear the filter before you save the workbook in an earlier Excel file format.

All filter state information remains available in the workbook, however, and is applied when the workbook is opened again in Excel 2007, unless the filter state information is edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate the data that has been filtered, and then you can clear the filter to unhide the rows that are hidden. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter. On the Home tab, in the Editing group, click Sort & Filter, and then click Clear to clear the filter.

Date grouping can also be turned off on the Advanced tab in the Excel Options dialog box. (Office Button Button image, Excel Options).

For more information, see:

Filter data in a range or table

Filter by using advanced criteria


Top of Page Top of Page

Formula issues

The following formula issues cause a significant loss of functionality:

Issue Solution
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.

In Excel 2007, workbook arrays that refer to other worksheets are limited by available memory, but in earlier versions of Excel, worksheets can only contain up to 65,472 workbook arrays that refer to other worksheets. Workbook arrays beyond the maximum limit will be converted to and display #VALUE! errors.

In the Compatibility Checker, click Find to locate cells that contain array formulas that refer to another worksheet, and then make the necessary changes to avoid #VALUE! errors.

For more information, see:

Overview of formulas

Replace a formula with its result

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.

In Excel 2007, the maximum length of formula contents is 8,192 characters and the maximum internal formula length is 16,384 bytes. In earlier versions for Excel, the maximum length of formula contents is only 1,024 characters, and the maximum internal formula length is only 1,800 bytes. When the combination of formula arguments (including values, references, and/or names) exceeds the maximum limits of earlier versions of Excel, the formulas will result in #VALUE! errors when you save the workbook to an earlier Excel file format.

In the Compatibility Checker, click Find to locate the cells that contain formulas that exceed the maximum formula length limits of earlier versions of Excel, and then make the necessary changes to avoid #VALUE! errors.

For more information, see:

Overview of formulas

Replace a formula with its result

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.

In Excel 2007, a formula can contain up to 64 levels of nesting, but in earlier versions of Excel, the maximum levels of nesting is only 7.

In the Compatibility Checker, click Find to locate the cells that contain formulas with more than 7 levels of nesting, and then make the necessary changes to avoid #VALUE! errors.

For more information, see:

Overview of formulas

Nest a function within a function

Replace a formula with its result

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.

In Excel 2007, a formula can contain up to 255 arguments, but in earlier versions of Excel, the maximum limit of arguments in a formula is only 30.

In the Compatibility Checker, click Find to locate the cells that contain formulas with more than 30 arguments, and then make the necessary changes to avoid #VALUE! errors.

For more information, see:

Overview of formulas

Replace a formula with its result

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.

In Excel 2007, the maximum number of operands (operand: Items on either side of an operator in a formula. In Excel, operands can be values, cell references, names, labels, and functions.) that can be used in formulas is 1,024, but in earlier versions of Excel, the maximum limit of operands in formulas is only 40.

In the Compatibility Checker, click Find to locate the cells that contain formulas with more than 40 operands, and then make the necessary changes to avoid #VALUE! errors.

For more information, see:

Overview of formulas

Replace a formula with its result

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.

In Excel 2007, a user-defined function (UDF) that you create by using Visual Basic for Applications (VBA) can contain up to 60 arguments, but in earlier versions of Excel, the number of arguments in UDFs are limited by VBA to only 29.

In the Compatibility Checker, click Find to locate the cells that contain functions with more than 29 arguments, and then make the necessary changes to avoid #VALUE! errors. You may need to use Visual Basic for Applications (VBA) to change user-defined functions.

For more information, see:

Overview of formulas

Replace a formula with its result

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.

Excel 2007 provides the following functions that are not available in earlier versions of Excel:

When you save the workbook in Excel 97-2003 file format, and open it in an earlier version of Excel, any new function will be displayed in the cell with _xlfn. For example, =_xlfn.IFERROR (1,2).

In the Compatibility Checker, click Find to locate the cells that contain functions that are new in Excel 2007, and then make the necessary changes to avoid #NAME? errors.

For more information, see:

Overview of formulas

Replace a formula with its result

Some formulas contain references to tables that are not supported in the selected file format. These references will be converted to cell references.

In Excel 2007, you can use structured references to make it much easier and more intuitive to work with table data when you are using formulas that reference a table, either portions of a table or the entire table. This feature is not supported in earlier versions of Excel, and structured references will be converted to cell references.

In the Compatibility Checker, click Find to locate the cells that contain formulas with structured references to tables, so that you can change them to the cell references that you want to use.

For more information, see:

Overview of formulas

Using structured references with tables

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.

In Excel 2007, you can use structured references to make it much easier and more intuitive to work with table data when you are using formulas that reference a table, either portions of a table or the entire table. This feature is not supported in earlier versions of Excel, and structured references will be converted to cell references. However, if the structured references point to tables in other workbooks that are not currently open, they will be converted to and displayed as #REF errors.

In the Compatibility Checker, click Find to locate the cells that contain formulas with structured references to tables in other workbooks, so that you can change them to avoid #REF errors.

For more information, see:

Overview of formulas

Using structured references with tables


The following formula issues cause a minor loss of fidelity:

Issue Solution
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.

Array formulas that refer to an entire column in Excel 2007will be converted to and displayed as #NUM! errors when they are recalculated in earlier versions of Excel.

In the Compatibility Checker, click Find to locate the array formulas that refer to an entire column so that you can make the necessary changes to avoid #NUM errors.

For more information, see:

Overview of formulas

One or more defined names in this workbook contain formulas that use more than the 255 characters allowed in the selected file format. These formulas will be saved but will be truncated when edited in earlier versions of Excel.

When named ranges in formulas exceed the 255 character limit that is supported in earlier versions of Excel, the formula will work correctly, but it will be truncated in the Name dialog box and cannot be edited.

In the Compatibility Checker, click Find to locate cells that contain named ranges in formulas, and then make the necessary changes so that users can edit the formulas in earlier versions of Excel.

For more information, see:

Overview of formulas

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.

When formulas in a workbook are linked to other workbooks that are closed, they can only display up to 255 characters when they are recalculated in earlier versions of Excel. The formula results may be truncated.

In the Compatibility Checker, click Find to locate cells that contain formulas that link to other workbooks that are closed so that you can verify the links and can make the necessary changes to avoid truncated formula results in earlier versions of Excel.

For more information, see:

Overview of formulas

A Data Validation formula has more than 255 characters.

When Data Validation formulas exceed the 255 character limit that is supported in earlier versions of Excel, the formula will work correctly, but it will be truncated and cannot be edited.

In the Compatibility Checker, click Find to locate cells that contain Data Validation formulas, and then use fewer characters in the formula so that users can edit them in earlier versions of Excel.

For more information, see:

Overview of formulas


Top of Page Top of Page

Conditional formatting issues

The following conditional formatting issues cause a significant loss of functionality:

Issue Solution
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.

In Excel 2007, conditional formatting can contain up to sixty-four conditions, but in earlier versions of Excel, only three conditions are supported.

In earlier versions of Excel, users will see the first three conditions only. All conditional formatting rules remain available in the workbook, however, and are applied when the workbook is opened again in Excel 2007, unless the rules are edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate cells that have conditional formatting applied that use more than three conditions, and then make the necessary changes.

For more information, see:

Add, change, or clear conditional formats

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.

In Excel 2007, conditional formatting ranges in cell can overlap, but this is not supported in earlier versions of Excel and conditional formatting is not displayed as expected.

In earlier versions of Excel, users will see different conditional formatting than expected. All conditional formatting rules remain available in the workbook, however, and are applied when the workbook is opened again in Excel 2007, unless the rules are edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate cells that have overlapping conditional formatting ranges, and then make the necessary changes.

For more information, see:

Add, change, or clear conditional formats

Manage conditional formatting rule precedence

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.

Excel 2007 provides the following new conditional formatting types that are not supported in earlier versions of Excel:

  • Color scales
  • Data bars
  • Icon sets
  • Top or bottom ranked values
  • Above or below average values
  • Unique or duplicate values
  • Table column comparison to determine which cells to format

In earlier versions of Excel, users will not see conditional formatting, such as data bars, color scales, or icon sets. All conditional formatting rules remain available in the workbook, however, and are applied when the workbook is opened again in Excel 2007, unless the rules are edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate cells that have conditional formatting types that are new in Excel 2007, and then make the necessary changes.

For more information, see:

Add, change, or clear conditional formats

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.

In Excel 2007, you can apply conditional formatting without stopping when the condition is has been met. This is not supported in earlier versions of Excel.

In the Compatibility Checker, click Find to locate cells that contain conditional formatting with the 'Stop if True' option cleared, and then click Fix to resolve the compatibility issue.

In earlier versions of Excel, the conditional formatting results will not be the same as in Excel 2007, because conditional formatting is no longer applied after the first condition is true. All conditional formatting rules remain available in the workbook, however, and are applied when the workbook is opened again in Excel 2007, unless the rules are edited in the earlier version of Excel.

For more information, see:

Add, change, or clear conditional formats

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 is not supported in earlier versions of Excel.

In Excel 2007, you can apply conditional formatting to ranges that are not adjacent. This is not supported in earlier versions of Excel.

In earlier versions of Excel, users will not see conditional formatting in nonadjacent cells. All conditional formatting rules remain available in the workbook, however, and are applied when the workbook is opened again in Excel 2007, unless the rules are edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate cells that contain a conditional formatting type on a nonadjacent range, and then make the necessary changes.

For more information, see:

Add, change, or clear conditional formats

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.

Conditional formatting that is applied to Excel 2007 PivotTables does not display the same results in PivotTables in earlier versions of Excel.

In earlier versions of Excel, the conditional formatting results in the PivotTables will not be the same as in Excel 2007. All conditional formatting rules remain available in the workbook, however, and are applied when the workbook is opened again in Excel 2007, unless the rules are edited in the earlier version of Excel.

In the Compatibility Checker, click Find to locate PivotTable fields that contain conditional formatting rules, and then make the necessary changes.

For more information, see:

Add, change, or clear conditional formats


Top of Page Top of Page

Graphics, object, and ActiveX control issues

The following graphics, object, and ActiveX issues cause a significant loss of functionality:

Issue Solution
Any effects on this object will be removed. Any text that overflows the boundaries of this graphic will appear clipped.

In Excel 2007, you can use special effects, such as transparent shadows that are not supported in earlier versions of Excel. The special effects will be removed. Also, in Excel 2007, when you insert text in a shape that is longer than the shape, the text spills outside of the boundaries of the shape. In earlier versions of Excel, this text will be truncated. To avoid truncated text, you can adjust the size of the shape for a better fit.

In the Compatibility Checker, click Find to locate the objects that have special effects applied so that you can remove the special effects as needed.

For more information, see:

Add or delete a fill or effect in your SmartArt graphic

Resize a shape or an entire SmartArt graphic

An embedded object in this worksheet is created in a newer version of Office. You cannot edit it in an earlier version of Excel. Embedded objects that are created in Excel 2007 cannot be edited in earlier versions of Excel.
Uninitialized ActiveX controls cannot be transferred to the selected file format.  The controls will be lost if you continue.

If a workbook contains ActiveX controls that are considered to be Unsafe for Initialization (UFI), they are lost when you save the workbook to an earlier Excel file format. You may want to mark those controls as Safe for Initialization (SFI).

If you open a workbook with uninitialized ActiveX controls that are set to high security, you must first use the Message Bar to enable them before they can be initialized.

For more information, see:

Enable or disable ActiveX controls in Office documents

Enable or disable security alerts on the Message Bar


Top of Page Top of Page

Customization issues

The following customization issue causes a minor loss of fidelity:

Issue Solution
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.

Because the Microsoft Office Fluent user interface in Excel 2007 is very different from the menus and toolbars in earlier versions of Excel, any customizations that were made in Excel 2007 are not available in earlier versions of Excel.

In earlier versions of Excel, you can add similar custom commands to toolbars and menus.


Top of Page Top of Page

Collaboration issues

The following collaboration issue causes a minor loss of fidelity:

Issue Solution
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.

To keep sharing the shared workbook with users who use earlier versions of Excel and to ensure that all shared workbook features are available in the earlier versions, you should save the workbook in the Excel 97-2003 file format.

For more information, see:

Use Office Excel 2007 with earlier versions of Excel

Use a shared workbook to collaborate


Top of Page Top of Page

 
 
Applies to:
Excel 2007