Formula compatibility issues

The Compatibility Checker found one or more formula-related compatibility issues.

Important: Before you continue saving the workbook to an earlier file format, you should address issues that cause a significant loss of functionality to prevent permanent loss of data or incorrect functionality. Issues that cause only a minor loss of fidelity might or might not need to be resolved before you continue saving the workbook — data or functionality is not lost, but the workbook might not look or work exactly the same way when you open it in an earlier version of Excel.

Compatibility issues that cause a significant loss of functionality

Compatibility issues that cause a minor loss of fidelity

Compatibility issues that 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 only 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 of Excel, the maximum length of formula contents is 1,024 characters, and the maximum internal formula length is 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 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 VBA code 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 then open it in an earlier version of Excel, any new function will be displayed in the cell with the prefix_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

Top of Page

Compatibility issues that 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 might be truncated.

In the Compatibility Checker, click Find to locate cells containing formulas that link to other workbooks that are closed so that you can verify the links and 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

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×