New conditional formatting "rules" ...
A few posts back when I summarized our goals for conditional formatting, I said that one of our goals was to make a greater number of scenarios possible without needing to write formulas. Let me briefly explain what I mean. Excel 2003 gives users two choices when creating conditional formats. Users can either set up numerical comparisons (this cell is greater than a value, less than a value, equal to a value, etc.), or they can use a formula to specify conditions. While formulas are powerful and flexible, they take more time and knowledge of the product, so it can be challenging to set up conditional formats like “highlight the top 20% of this range”. As part of planning Excel 12, our team did a bunch of research in this area, and we found that there were a set of very common things people wanted to do with conditional formatting that while possible, required writing formulas, not all of which were obvious. Based on this research, we have added a range of new rules to conditional formatting to address the most common customer requests, and we think this will mean a much larger number of people can benefit from the value of the feature. We have broken these rules into a couple of categories in our new UI to make it easier to find the correct rule type (more on that in the next post). Specifically, in Excel 12, without writing formulas, you can conditionally format cells:
- With data bars, color scales, or icons based on the numeric value in the cell, percentages, percentiles, or a formula. See the posts on data bars, color scales, and icon sets for more information on each of these.
- Containing, not containing, beginning with, or ending with specific text. For example, highlighting parts containing certain characters in a parts catalog.
- Containing dates that match dynamic conditions like yesterday, today, tomorrow, in the last 7 days, last week, this week, next week, last month, this month, next month. For example, highlight all items dated yesterday. The great part about these conditions is that Excel handles calculating the date based on the system clock, so the user doesn’t need to worry about updating the condition.
- That are blank or that are non-blank.
- That have errors or that do not have errors.
- That are in the top n of a selected range (where n is whatever number you want) OR that are in the top n percent of a selected range (again, where n is adjustable). For example, highlighting the top 10 investment returns in a table of 1,000 investments.
- Cells that have the bottom n values OR cells that are the bottom n percent of a selected range.
- Cells that are above average, below average, equal to or above average, equal to or below average, 1 standard deviation above, 1 standard deviation below, 2 standard deviations above, 2 standard deviations below, 3 standard deviations above, 3 standard deviations below a selected range.
- Cells that are duplicate values or, conversely, cells that are unique values.
- Based on comparisons between two columns in tables. For example, highlight values where values in the “Actual Sales” column are less than in the “Sales Target” column. When working with tables, we have also made it easy to format the entire row based on the results of a condition. More on this when I talk about tables in a few weeks.
Note, in Excel 12, you can still create the conditional formatting rules types from Excel 2003. Next up, I will cover the UI in the Ribbon for adding and removing conditional formats.
Before I sign off, though, I wanted to correct something I said in a comment last week. Rob van Gelder asked if we had added support for applying number formats to cells using conditional formatting (for example, if a cell is equal to 0, apply a format that makes the cell blank). I answered that we had not. The good news is that we have, which opens up a range of possibilities. I will talk a bit more about these and other limits we changed later this week.
