Using Icon Sets for conditional formatting in Microsoft Excel 2007
This article looks at one of the new features in Conditional Formatting in Excel 2007/2010 which we can use to make our boring lists more interesting!

Posted by Michael Allsop on 19 October 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

'RAG' Status Conditional Formatting

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

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:

The Conditional Formatting drop down menu

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:
The 'Edit Rule...' dialog box

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!

Icon Sets in Excel

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

Do remember that the fancy new conditional formatting options won't work in earlier versions of Excel, and they'll be removed if you have to save the file in Excel 97 - 2003 format!

This blog has 0 threads Add post