Conditional Formatting

Scott Ruble has a post on on the Excel team blog today, asking for feedback on the databars functionality that was introduced in Excel 2007. This is a very popular new capability, and part of a more general concept known as "conditional formatting," where various types of formatting are applied to cells to indicate the relative magnitude of the values in those cells.

Scott's post presents a few scenarios and asks for feedback on what people would like to see done in those cases. If you're a conditional formatting user, please drop in and share your opinions so that the Excel team can benefit from your feedback.

Here, I'm going to talk a bit about how conditional formatting is stored in Open XML spreadsheet documents, for developers who want to include conditional formatting in documents they generate.

Let's start with a simple example of conditional formatting, a sample document from the developer workshop content on OpenXMLDeveloper.org:

This example shows two types of conditional formatting: dataBar (as in Scott's example) is used in the first column, and the other two columns use iconSet formatting. The iconSet option allows for various collections of icons to be used, and these two examples are 3Arrows and 4Rating, respectively.

Conditional formatting information is stored in the worksheet part as conditionalFormatting elements, which occur after the sheetData element. The worksheet data values are stored in sheetData, and the sqref attribute of conditionalFormatting specifies the range of cells that each rule applies to.

Once you understand this basic structure, most of the details are simple. You can add a showValue="0" attribute added to dataBar element to supress display of the actual numeric values, for example. For the iconSet rules, you can specify any one of 17 pre-defined lists of icons, as specified in section 3.18.44 ST_IconSetType (Icon Set Type). For more information on conditional formatting rules in general, see section 3.3.1.9 (cfRule) and 3.3.1.17 (conditionalFormatting).

In some cases you might want the icon-set conditional formatting to distribute the icons non-uniformly across the range of values. For example, instead of having red/yellow/green correspond to the lowest third, middle third, and highest third of the range, you might want to only use the red or green icon for the bottom or top 10% of the range. In that case, you'd change the values in the cfvo elements (conditional formatting value objects) to 10, 80, and 10, respectively.

Conditional formatting is a great way to enhance spreadsheets, and it's very easy to do.