Conditional Formatting Trick 2 – How to get an icon in some cells, but not all cells
A few months ago, I described the new features we have added to Excel 2007 in the area of conditional formatting. One of the new formats we added is called a “icon set” … check out this earlier post for a refresher, but the basic idea is that Excel draws an icon in each cell representing the value of that cell relative to the other cells in the selected range. Here is a shot from that post.
(Click to enlarge)
The Excel 2007 UI allows you to choose from a wide range of icon sets to apply to your document, but, but, by default, all the cells that you apply conditional formatting to get an icon. For example, say you had this range of data (measurements of something over 4 time periods) …
… and you applied the “three symbols” icon set using the ribbon …
… you would end up with something like this. Excel would evaluate all the cells and show an icon in each cell, thereby communicating the value of each cell relative to all cells in the range.
Fair enough, and useful. One of the bits of beta feedback we have received, though, is it sure would be nice to be able to only show a single icon in a cell if the cell met some criteria – for example, you might just want to see a red “X” in any cells that have a value greater than 80 (because 80 is bad). While we did not have time to add a single rule to enable that in Excel 2007 (it is on the list for future consideration), it is possible, so I wanted to show folks how.
The first thing we need to do is to set the rule we just applied (the “three symbols” rule) so that the red “X” shows up for values greater than 80. That’s as simple as tweaking a setting in the Edit Rules dialog.
The next thing to do is to add a second rule that formats values that are less than 80, and set the format to “Automatic” (black text on transparent cells for the default theme).
Finally, make sure the “Stop If True” flag is turned on.
Once you press OK, you will see that indeed, only the values greater than 80 have an icon.
So how does this work? Excel evaluates the “Cell Value <80” rule on each cell first, and if the rule is true, Excel stops evaluating the conditions on that cell (that’s what the “Stop If True” flag does) and applies the format, which is set to black on white, so visually nothing changes in those cells. If the first rule is not true (meaning the cell has a number 80+), Excel evaluates the “three symbols” rule, and puts a red “X” in the cell. Because the other two symbols (yellow ! and green check mark) have the same condition as the first rule (<80), they are never true and hence they are never seen. Hopefully that makes sense.
The “Stop If True” setting turns out to be pretty handy, and I expect that users will find all sorts of creative uses beyond just this case.
PS updated to fix a typo
