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.
________________________