Conditional formatting compatibility issues for Excel

Some cells in this workbook contain conditional formatting, which refers to values on other worksheets. These conditional formats won't be saved.

What it means    Beginning in Excel 2007, conditional formatting rules can refer to values on other worksheets. These rules are not supported in earlier versions, and will be lost if you save the file in the Excel 97-2003 file format.

What to do    Click Find in the Compatibility Checker dialog box to locate each instance of this issue. For each case, copy the source data to a new range of cells on the worksheet containing the formatted cells and then update the conditional formatting rule to use the new range.

One or more cells in this workbook contain a conditional formatting icon set arrangement that is not supported in earlier versions of Excel. These conditional formats will not be saved.

What it means   New conditional formats were introduced in Excel 2007, including icon sets. The Excel 97-2003 file format does not support conditional formatting rules that use icon sets. If you save the file in that file format the conditional formatting for the cell(s) in question will be discarded in the saved file.

What to do   Click Find in the Compatibility Checker dialog box to locate each instance of the icon set arrangement. For each instance, choose a conditional format that is supported in Excel 97-2003, such as formatting the cell with a color. After you replace all the instances, you can save the file in the Excel 97-2003 file format.

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.

What it means    In Excel 2007 and later, conditional formatting can contain up to sixty-four conditions, but in Excel 97-2003, you will see the first three conditions only.

However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel 2007 and later, unless the rules were edited in Excel 97-2003.

What to do    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 to use no more than three conditions.

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.

What it means    Overlapping conditional formatting ranges are not supported in Excel 97-2003, and the conditional formatting is not displayed as expected.

However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel 2007 and later, unless the rules were edited in Excel 97-2003.

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

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.

What it means    In Excel 97-2003, you will not see conditional formatting types, such as data bars, color scales, icon sets, top or bottom ranked values, above or below average values, unique or duplicate values, and table column comparison to determine which cells to format.

However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel 2007 and later, unless the rules were edited in Excel 97-2003.

What to do    In the Compatibility Checker, click Find to locate cells that have conditional formatting types that are new in Excel 2007 and later, and then make the necessary changes to use only formatting types that are supported in the earlier versions of Excel.

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.

What it means    In Excel 97-2003, conditional formatting without stopping when the condition has been met is not an option. Conditional formatting is no longer applied after the first condition is true.

However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel 2007 and later, unless the rules were edited in Excel 97-2003.

What to do    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.

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.

What it means    In Excel 97-2003, you will not see conditional formatting in nonadjacent cells.

However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel 2007 and later, unless the rules were edited in Excel 97-2003.

What to do    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 to use conditional formatting rules that are available 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.

What it means    Conditional formatting results you see in Excel 97-2003 PivotTable reports will not be the same as in Excel 2007 and later PivotTable reports.

However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel 2007 and later, unless the rules were edited in Excel 97-2003.

What to do    In the Compatibility Checker, click Find to locate PivotTable report fields that contain conditional formatting rules, and then apply conditional formatting rules that are available in the earlier versions of Excel.

One or more cells in this workbook contain conditional formatting which refers to values on other worksheets. These conditional formats will not be supported in earlier versions of Excel.

What it means    In Excel 97-2003, conditional formatting that refers to values on other worksheets is not displayed.

However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel 2010 and later, unless the rules were edited in Excel 97-2007.

What to do    In the Compatibility Checker, click Find to locate cells that contain conditional formatting that refers to values on other worksheets, and then apply conditional formatting that does not refer to values on other worksheets.

One or more cells in this workbook contain conditional formatting using the 'Text that contains' format with a cell reference or formula. These conditional formats will not be supported in earlier versions of Excel.

What it means     In Excel 97-2007, conditional formatting that use formulas for text that contains rules is not displayed on the worksheet.

However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel 2010 and later, unless the rules were edited in Excel 97-2007.

What to do    In the Compatibility Checker, click Find to locate cells that contain conditional formatting that uses formulas for text that contains rules, and then apply conditional formatting that is supported in earlier versions of Excel.

One or more cells in this workbook contain a rule that will not be supported in earlier versions of Excel because there is a formula error in its range.

What it means    In Excel 97-2007, conditional formatting that use range-based rules cannot be displayed correctly on the worksheet when the range-based rules contain formula errors.

What to do    In the Compatibility Checker, click Find to locate cells that contain range-based rules that contain formula errors, and then make the necessary changes so that range-based rules do not contain formula errors.

One or more cells in this workbook contain a conditional formatting icon set arrangement that is not supported in earlier versions of Excel.

What it means    In Excel 97-2007, conditional formatting that displays a specific icon set arrangement is not supported and the icon set arrangement is not displayed on the worksheet.

However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel 2010 and later, unless the rules were edited in Excel 97-2007.

What to do    In the Compatibility Checker, click Find to locate cells that contain conditional formatting that display a specific icon set arrangement, and then make sure that conditional formatting does not display that icon set arrangement.

One or more cells in this workbook contain a data bar rule that uses a "Negative Value" setting. These data bars will not be supported in earlier versions of Excel.

What it means    In Excel 97-2007, conditional formatting that contains a data bar rule that uses a negative value is not displayed on the worksheet.

However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel 2010 and later, unless the rules were edited in Excel 97-2007.

What to do    In the Compatibility Checker, click Find to locate cells that contain conditional formatting that contains negative data bars because the negative value format is set to Automatic in the New Formatting Rule dialog box (Home tab, Styles group, Conditional Formatting, New Rule) or the Axis Settings have been set to Automatic or Cell midpoint in the Negative Value and Axis Settings dialog box (Home tab, Styles group, Conditional Formatting, New Rule, Data Bar format style, Negative Values and Axis button), and then make the necessary changes.

One or more cells in this workbook contain conditional formatting which refers to more than 8192 discontinuous areas of cells. These conditional formats will not be saved.

What it means    In Excel 97-2007, conditional formatting that refers to more than 8192 discontinuous areas of cells is not displayed on the worksheet.

However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel 2010 and later, unless the rules were edited in Excel 97-2007.

What to do    In the Compatibility Checker, click Find to locate cells that contain conditional formatting that refer to more than 8192 discontinuous areas of cells, and then change the number of discontinuous areas of cells the conditional formatting refers to.

One or more cells in this workbook contain a data bar rule that uses a fill, border, or "bar direction" setting. These data bars will not be supported in earlier versions of Excel.

What it means    In Excel 97-2007, conditional formatting that contains a data bar rule that uses a solid color fill or border or left to right and right to left bar direction settings for data bars is not displayed on the worksheet.

However, all conditional formatting rules remain available in the workbook and are applied when the workbook is opened again in Excel 2010 and later, unless the rules were edited in Excel 97-2007.

What to do    In the Compatibility Checker, click Find to locate cells that contain a conditional formatting data bar rule that uses a solid color fill or border or left to right and right to left settings for data bars, and then and then make the necessary changes.