As many Excel 2007 and Excel 2010 users know, one of the enhanced conditional formats available in those versions of Excel is that of traffic lights. As shown in Figure 1, traffic light conditional formats allow users to segment or stratify data into three tiers – red light, yellow light, and green light. But what if we only wanted to see just the data that meets the green light criteria? How can we modify the conditional formatting rules in Excel 2007/2010 to change the way traffic light conditional formatting is applied? Fortunately, the answer is quite simple.
To begin, select the data range to which you want to apply your conditional format and from the Home tab of the Ribbon, click Conditional Formatting, Icon Sets, and choose the traffic light icons. Next, with the cells to which the conditional formats were applied still selected, again click Conditional Formatting on the Home tab of the Ribbon, followed by Manage Rules to open the Conditional Formatting Rules Manager dialog box shown in Figure 2.
In the Conditional Formatting Rules Manager, double-click on the rule you wish to modify – in this case, the rule labeled “Icon Set.” This opens the Edit Formatting Rule dialog box shown in Figure 3.
In the Edit Formatting Rule dialog box, click the drop-down arrow next to the yellow light icon and choose No Cell Icon as shown in Figure 4. Repeat the process for the red traffic light icon. Note that if you wish to change the calculations driving the application of the conditional formats, you can do that here as well.
Having edited the conditional format rule, click OK and return to the worksheet. Upon doing so, you will see that only the green traffic lights remain.
The conditional formatting enhancements made to Excel 2007 and Excel 2010 turned a good feature into a great one. Now, with just a little knowledge of how to modify the conditional formatting rules, you can use this powerful feature in ways you might not have thought possible.
For a video demonstration of this tip, please visit www.tinyurl.com/k2tips80.