Create Conditional Formatting Icons in Excel

Conditional Formatting Icons

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.

Conditional Formatting Icon Sets Command
Conditional Formatting Icon Sets Command

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.

Then, use an absolute reference to those cells in the conditional formatting formula.

conditional formatting rule with cell reference
conditional formatting rule with cell reference

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.

CondFormatCellRefCell________________________

7 thoughts on “Create Conditional Formatting Icons in Excel”

  1. 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. […] There are instructions here: Conditional Formatting Icons in Excel 2003 […]

  3. 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?

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

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.