BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
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:
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.
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!
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!