Using Icon Sets in Microsoft Excel 2007

One of the new features in the latest (2007 and 2010) versions of Microsoft Excel is the opportunity to add graphics to cells by applying conditional formatting.

Building on my earlier blogs looking at basic conditional formatting and data bars in Excel, this short blog will focus on the new cell icon options within Icon Sets.

Our starting point will be a short list of numbers, an extract of which is shown below:

'Traditional' conditional formatting would be to apply a red, yellow or green fill to the background of the cells. However, we can now use:

• Data Bars - which create a mini chart behind the data
• Color Scales - shade from 'hot' to 'cold'
• Icon Sets - preformatted traffic light icons or other representations of high to low values

Icon Sets

As with any formatting, select all the cells that you want to include within the conditional formatting rule. Then, on the Home tab, click on Conditional Formatting, hover over Icon Sets and pick a style:

Here I have picked the '3 Traffic Lights (Rimmed)' style

This will create a new rule in which the top 33% (based on the quantity of cells you have selected) have the green icon, the middle third the yellow, and the bottom third the red icon.

Manage Rules...

If this is the analysis you were wanting, then great! If, however, you wanted the traffic lights to change colour at preset thresholds, then let's have a look at how to fine-tune this rule:

• Make sure you've still got all the affected cells selected
• Click on Conditional Formatting -> Manage Rules...
• Double click on your Icon Sets rule, and make the changes below:

I'll complete this by entering 10 as my 'yellow' threshold value

Once you have made the changes, click on OK on the Edit Rule... dialog box, then click OK or Apply on the Conditional Formatting dialog box and your edited rule should now be in place!

The finished list with 'Icon Sets' replacing the old-fashioned conditional formatting!

Comments on this blog

This blog currently has no comments.