Conditional Formatting Compatibility

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

Conditional Formatting is one of the areas where we have made a lot of improvements in for Excel 2007.  Dave has already described in previous posts what we have done; here is a recap of our investments:

• Three new visualizations – “data bars”, “color scales”, and “icon sets”
• New UI for adding, removing, and managing conditional formats 
• New conditional formatting “rules” 
• Increased “limits” and other customer requests 
• Some PivotTable-specific functionality 
• Some Table-specific functionality

I want to describe in more detail what to expect using these features in Excel 2007 when sharing workbooks with others using previous versions of Excel.

Formatting Retained

In general all of the conditional formatting is retained when saved to the Excel 97-2003 file format.  Even though previous versions of Excel don’t have the same functionality, we do save the conditional formatting to the file and the file can be opened and saved in previous versions of Excel without losing conditional formatting.

What does it look like?

Previous versions of Excel cannot display the new data visualizations that we have added.  In the example below I have added 3 different types of visualizations and saved to the Excel 97-2003 file format.  The pictures show what you will see in Excel 2007 and previous versions.

Can I edit the file?

Files that have new conditional formatting stored in them can be edited in previous versions.  You can change values in cells, sort ranges, add formatting and do a number of other tasks without disturbing the conditional formatting.  In general if you don’t make changes directly to the conditional formatting on a range, it will safely round-trip back to Excel 2007.

For example, using the same file, I opened it in Excel 2003, cleared a few cells, applied bold formatting, changed number formats and sorted a range.  I saved the file and then opened it in Excel 2007.  Here is the result:

Designing formats for use in multiple versions

If you want to create workbooks in Excel 2007 with conditional formatting that looks the same in previous versions, you can do so by avoiding the new visualizations and new rules.  Specifically, here is a list of new conditional formatting features that affect the result in previous versions:

• More than 3 conditions used for formatting – Previous versions of Excel limited the conditions to 3 per cell.  You can use more than 3 and they will be retained, but only the first three conditions will be displayed in earlier versions of Excel.
• Conditional Formatting that overlaps – Excel 2007 let’s you define overlapping conditional formatting and previous versions of Excel will not evaluate all of the rules, so these cells may show different formatting.
• New visualizations – As discussed above data bars, color scales and icon sets cannot be displayed in earlier versions.
• “Stop if True” – Excel 2007 has a new conditional formatting option to process additional formatting rules even when a previous condition is true.  Earlier versions of Excel do not recognize this option and will stop after the first true condition.
• 'Top 10' or 'Compare to Average' - Earlier versions of Excel do not have this ability and will calculate the condition across all values.
• Non-contiguous Formatting – Excel 2007 supports additional conditional formatting on ranges that are not adjacent to each other. This conditional formatting type is not supported in earlier versions of Excel."
• Conditional formatting of PivotTables – There are new types of formatting in Excel 2007.  I will cover this in more detail in an upcoming post about the compatibility of PivotTables.

Finding Incompatibilities

As you are using conditional formatting the Compatibility Checker is designed to help you to identify and find the above issues in your workbook.  Use the Compatibility Checker to make decisions about whether you need to change conditional formatting to ensure greater visual compatibility with previous versions of Excel.

Next up – Compatibility of other Formatting