I really like the new icon sets that are in Excel 2007. They’re kind of a neat way to format a cell to show interpretive information at a glance.
One practical place to use these is as an alternative to the using custom number formats that I blogged about last year. I decided to use Excel’s icon sets to show a green check when something was positive and a red x when negative, as shown below:
One thing that can be frustrating when trying to set this up is that Excel forces you to use the three part icon set. By default, the image above would have a yellow exclamation beside the zero. Personally, I don’t need it. But with Excel forcing it on you, how do you avoid it? Here’s how I did it:
Select the cells:
From the Home tab, choose Conditional FormattingÃ Icon SetsÃ More Rules
Using the default of “Format All Cells Based On Their Values”, set up the rule as follows:
You’ll need to change:
- The “Type” fields to “Number”
- The Icon Style
- Apply the rule
Now you have the cells looking as shown below, but how do you hide the yellow exclamation on the zero?
The answer is to set up a conditional format using another of Excel 2007′s conditional format tools; Stop if True
- Select the cells again
- This time choose “New Rule” from the Conditional Formatting menu (not “More Rules” from the icon set area)
Choose to “Format only cells that contain” and set up the rule as shown below:
Applying the rule now won’t have any effect, as it is essentially a blank rule. (See the message about “No Format Set”?)
- Click Format
- On the “Fill” tab, click “No Color” for the background and click OK twice
We now have our second rule set up, but nothing appears different. Let’s fix that:
- With the cells still selected, go back and choose “Manage Rules” from the Conditional Formatting menu
Check the “Stop if True” box beside your latest rule as shown:
Click Apply and it should the yellow exclamation disappears from the scenario.
The “Stop if True” feature is a great addition to Excel’s conditional formatting as it allows us to decide just how many of our formats we wish to overlay in 2007. Depending on how many you apply to one cell though, you may have issues with trying to figure out inheritance issues, so use them carefully.