In Excel 2007, conditional formatting options include Icon Sets, such as coloured flags, stop lights, and other symbols.
These icon sets aren’t available in earlier versions of Excel, but here’s how you can create your own conditional formatting icons in Excel 2003.
TIP: You can use this technique in newer versions of Excel too, if you can’t find an existing icon set with the shapes and colours that you need
Conditional Formatting Icon Sets
First, in case you haven’t see the icon sets before, here’s the menu option for adding icon sets in Excel 2007.
You can use these symbols to show what values are going up, or which departments are exceeding their budgets.
Excel 2003
Icon Sets aren’t available in earlier versions of Excel, but you can use conditional formatting, a formula, and the Wingding font to show symbols in an adjacent column.
In this example, coloured shapes appear in cells C3:C7, linked to the values in column B.
- For values less than 10, a red circle will appear
- For values greater than 30, a green square will appear.
- For all other values, a yellow diamond will appear.
Create the Formulas
You’ll create an IF formula, and use the WingDing font to show the result as a symbol. In WingDing font, l (lower case L) is a circle, n is a square and t is a diamond.
- In cell C3 enter the first formula:
=IF(B3=””,””,IF(B3<10,”l”,IF(B3>30,”n”,”t”))) - Copy the formula down to cell C7
- Format cells C3:C7 with Wingding font, and yellow font color. This is the default colour, for the middle value cells.
Add the Conditional Formatting
Next, you’ll add conditional formatting, to colour the red and green shapes.
- Select cells C3:C7
- Choose Format|Conditional Formatting
- From the first dropdown, choose Formula Is
- For the formula, enter: =$B3<10
- Click the Format button, select Red as the font colour, then click OK.
- Click the Add button, and for Condition 2, choose Format|Conditional Formatting
- From the first dropdown, choose Formula Is
- For the formula, enter: =$B3>30
- Click the Format button, select Green as the font colour, then click OK.
- Click OK to close the Conditional Formatting dialog box.
Test the Conditional Formatting
To test the conditional formatting, change one or more of the values in column B.
For example, type a 5 in cell B5, and the shape in cell C5 should change to a red circle.
Make the Conditional Formatting Flexible
To make the conditional formatting easier to change, you could enter the low and high cutoff values on the worksheet.
Then, use an absolute reference to those cells in the conditional formatting formula.
Change Worksheet Formulas
Also, change the worksheet formulas, so they also refer to the cutoff values on the worksheet:
- =IF(B3=””,””, IF(B3<$F$1,”l”, IF(B3>$F$2,”n”,”t”)))
Now you can change the limits on the worksheet, without editing the formulas and conditional formatting.
________________________
Brilliant solution!
That’s a really clever way to get around the limitation of Excel 2003.
You can use the arrow types of the Wingdings font by opening the CharMap. You will find it on Start>All Programs>Accessories>System Tools>Character Map. Then just select, copy & paste the character into your Excel workbook.
=)
Thanks Aurorus, the arrows are a great idea.
[…] There are instructions here: Conditional Formatting Icons in Excel 2003 […]
Is there a way to do conditional formatting of images within Excel 2003? So, I have a map of the US. If I have a “1” in a cell, I’d like to turn California red; a 2 would make CA blue, etc.
Is this possible?
@John, you can do this with programming and there is an example on Tushar Mehta’s website:
http://www.tushar-mehta.com/publish_train/xl_vba_cases/0301-dashboard-conditional_shape_colors.htm
I have spent the last three days trying to figure out how to create a stop light dashboard with customized colors. This finally did it for me!! Thank you very much for the help.