More about conditional formatting ...
Another goal that we had for conditional formatting was to address top customer requests, like more than three conditions, the ability to reorder conditions, and the ability to have more than one condition resolve true. Let’s take a look at some of the more advanced conditional formatting UI and how it allows us to address those customer requests (again, UI is not final). First, let’s look at the “Conditional Formatting Rules Legend” dialog:
The Conditional Formatting Rules Legend dialog is the one-stop place to view and manage all the conditional formats in a workbook. Using this dialog, you can:
- View existing rules – you can view the rule condition, the range it is applied to, and the format set on the rule. The ‘Show Formatting Rules for’ dropdown at the top of the dialog allows you to change the scope of the rules you are looking at. You can view rules for the selected cells, the current worksheet, the current Table, other worksheets in the book, other Tables in the workbook, or the entire workbook.
- Add new rules via the New Rule button. More on this below.
- Change the order in which rules are evaluated. This is done using the up and down arrow buttons.
- Edit existing rules via the Edit Rule button. The Edit Rule UI is the same as the "New Rule" UI explained below.
- Delete rules via the Delete Rule button.
- Control whether more than one Rule can evaluate to true. More on this below.
When you press New Rule on the Conditional Formatting Rules Legend dialog, or when you select More Options … from the conditional formatting gallery fly-outs, you will see the “New Rule” dialog:
This dialog allows you to add new conditional formats to the selected range. Through this UI, you can add all the conditional formats that are available through the ribbon as well as a set of additional, slightly more sophisticated conditional formats. In addition, you can change the different settings and parameters on any conditional format. We have broken down our conditional formats into a few categories which are listed in the top of the New Rule dialog under “Select a Rule Type”. The bottom part of the dialog contains the Rule Description which changes based on the rule type selected above. Here is a summary of the categories:
- Format all cells based on their values – Use this to create a data bar, 2-color or 3-color color scale, or icon set rule.
- Format only cells that contain… – Use this to create the Excel 2003-style rules and more (format cells greater than, less than, greater than or equal to, less than or equal to, equal to, not equal to, between, not between). This is also the entry point to create rules of type: specific text, date occurring, blanks, non-blanks, errors, non-errors.
- Format only top or bottom ranked values – Use this to create top n, top n%, bottom n, bottom n% rule types.
- Format only values that are above or below average – Use this to create above average, below average, 1 or 2 or 3 standard deviation above, or 1 or 2 or 3 standard deviation below rule types.
- Format only unique or duplicate values – Use this to create rules that format unique or duplicate values.
- Use a formula to determine which cells to format – Use this to create Excel 2003-style rules where you can enter a formula to determine whether a format should be applied.
Finally, as I touched on briefly in one of my initial posts, we have also made some significant improvements in the infrastructure of conditional formatting based on top customer requests. We changed the following in Excel 12:
- Number of conditions on a range is no longer limited by number. In Excel 2003, you could create conditional formatting rules with 3 conditions. Now, you are no longer limited by number; you are only limited by system memory.
- Multiple conditions evaluating to true. In Excel 2003, there could only be one conditional format applied to a given cell. If more than one conditional format evaluated to true, then only the format associated with the first conditional format was applied. We now allow you to have multiple conditional formats on a cell applied if more than one condition evaluates to true. For example, assume you have one conditional format that makes font style bold when true, and another that makes the cell background colour red when true. If both conditions are true, you will see both formats applied (bold text and red cell background color). When the formatting is conflicting (for example red font and green font), the first rule wins. This can be turned on or off for any rule using the checkboxes on the Conditional Formatting Rules Legend dialog.
- References to other worksheets. In Excel 2003, you could not reference ranges on other worksheets in a condition. Excel 12 allows cell references anywhere in the workbook.
- Support for Number formatting in conditional formatting. In Excel 12, the Number tab in the Format Cells dialog will be available in conditional formatting so you can set conditional formatting to be number formatting.
With that, we have largely summarized our investment in conditional formatting for Excel 12. Next time, I will briefly cover how this works in the object model, and then on to formulas and functions.
