This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Add Icons to Only the Good Cells
Problem: I want to mark only the best (or worst) cells with an icon. Everything else should not be marked with any icon.
Strategy: Microsoft added a new check box called Stop if True to the conditional formatting rules. This check box allows you to achieve the effect you’re looking for.
Say that you want to add a gold star to all scores of 100.
First, you set up a three-icon set and make sure the gold stars display for >=100. Next, you set up a conditional formatting rule that says do nothing if the value is less than 100. Because Excel evaluates the most recent rule first, you can tell Excel to stop evaluating more rules if the less-than-100 rule is true. This prevents Excel from ever drawing in other icons from the set.
5. Change the first value to >=100. Change the second value to anything.
6. Click OK. You now how gold star for 100, but all the other cells also get an icon.
7. With the same range selected, choose Home, Conditional Formatting, New Rule.
8. In the top of the New Formatting Rule dialog, under Select a Rule Type, choose Format Only Cells That Contain.
9. In the Edit the Rule Description section, choose Format Only Cells with Cell Value Less Than 100. Normally, you would then click the Format button and choose some exotic format, but in this case, you want the cells less than 100 to look just like every other cell, so, don’t click the Format button.
10. Click OK. Absolutely nothing will change.
11. Select Home, Conditional Formatting, Manage Rules. The Conditional Formatting Rules Manager dialog shows the most recent rule at the top. Click the Stop if True check box for the first rule, as shown below. This tells Excel that if the value is less than 100, it should apply no special formatting and not proceed to the icon rule.
Figure 1113 Indicate to stop if true.
Excel displays only the gold stars.
Figure 1114 Anything without an icon is caught by the first rule.