Conditional Formatting Icons in Excel 2003

In Excel 2007, conditional formatting options include Icon Sets, such as coloured flags, stop lights, and other symbols.

CondFormatIcon2007

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.

CondFormatIcon2003

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.

  1. In cell C3 enter the first formula:
    =IF(B3=””,””,IF(B3<10,”l”,IF(B3>30,”n”,”t”)))
  2. Copy the formula down to cell C7
  3. 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.

  1. Select cells C3:C7
  2. Choose Format|Conditional Formatting
  3. From the first dropdown, choose Formula Is
  4. For the formula, enter: =$B3<10
  5. Click the Format button, select Red as the font colour, then click OK.
    CondFormatFont
  6. Click the Add button, and for Condition 2, choose Format|Conditional Formatting
  7. From the first dropdown, choose Formula Is
  8. For the formula, enter: =$B3>30
  9. Click the Format button, select Green as the font colour, then click OK.
    CondFormat2003
  10. 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, and use an absolute reference to those cells in the conditional formatting formula.

CondFormatCellRef

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”)))

CondFormatCellRefCell

Now you can change the limits on the worksheet, without editing the formulas and conditional formatting.

________________________

You may also like...

7 Responses

  1. Aurorus says:

    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.
    =)

  2. Thanks Aurorus, the arrows are a great idea.

  3. Contextures Blog » Customize Excel Conditional Formatting Icons says:

    […] There are instructions here: Conditional Formatting Icons in Excel 2003 […]

  4. John says:

    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?

  5. Rick says:

    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.

  1. May 20, 2013

    […] There are instructions here: Conditional Formatting Icons in Excel 2003 […]

Leave a Reply to John Cancel reply

Your email address will not be published. Required fields are marked *