Conditional formatting doesn't work in earlier versions of Excel

Cause: One or more cells in the workbook contain data bars, color scales, or icon sets, which will not be displayed in earlier versions of Excel.

Solution:    Use classic conditional formatting.

Even if you choose not to replace the formatting in this workbook, any conditional formatting rules that you created remain available and are applied when the workbook is opened again in Excel for Mac 2011, or Excel 2007 for Windows and later, unless the rules were edited in an earlier version of Excel.

  1. To change the formatting, on the Compatibility Report, in the Results box, click the "Workbook contains conditional formatting that cannot be displayed" error. This selects the range where the conditional formatting is applied.

    If multiple ranges have conditional formatting, the Results box shows an error for each range.

  2. On the Home tab, under Format, click Conditional Formatting.

    Home tab, Format group

  3. Point to Clear Rules, and then click Clear Rules from Entire Sheet.

  4. Select the data again, and then on the Home tab, under Format, click Conditional Formatting, point to Highlight Cells Rules or Top/Bottom Rules, and then select from options there. These are classic formatting types that are compatible with earlier versions.

Cause: Some cells have more conditional formats than are available in earlier versions of Excel. Only the first three conditions will be displayed in those versions.

Solution:    Use a maximum of three conditional formatting rules.

Even if you choose not to replace the formatting in this workbook, any conditional formatting rules that you created remain available and are applied when the workbook is opened again in Excel for Mac 2011, or Excel 2007 for Windows and later, unless the rules were edited in an earlier version of Excel.

  1. To change the formatting, on the Compatibility Report, in the Results box, click the "Workbook contains conditional formatting that cannot be displayed" error. This selects the range where the conditional formatting is applied.

    If multiple ranges have conditional formatting, the Results box shows an error for each range.

  2. On the Home tab, under Format, click Conditional Formatting, and then click Manage Rules.

    Home tab, Format group

  3. Make sure that no more than three rules are present. To delete a rule, select the rule, and then click Delete_selected_rule .

    Repeat these steps for all the errors listed in the Results box.

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!

×