Create Your Own Excel Icon Set

Today, we’ll use ingenuity to overcome a limitation with Icon Sets. These were added to conditional formatting in Excel 2007, and you can use the icons to highlight the results in a group of cells. In this example, higher sales numbers show a green up arrow.

createicons02

This feature was improved in Excel 2010, and now you can customize these sets, creating your own mix from the existing icons.

createicons03

What happens if those numbers represent errors, instead of sales? Now the lower numbers are better, so the higher numbers should show a red up arrow. Unfortunately, you can't change the color of the icons. If you want a red Up Arrow, instead of green, you’re out of luck!

createicons04

Create Your Own Icons

If you can’t find the icons that you need, you can create your own set. Set up a lookup table with the values and symbols for your formatting rules. Then, add a formula and formatting in an adjacent cell, to show the applicable icon.

In the screen shot below, column B shows the built-in icons, for numbers 10 to 100. In column C, you can see the symbols that were created – a red up arrow for high numbers and green down arrow for low numbers.

createicons01

Create the Lookup Table

First, set up the lookup table

  1. In G3:G5, type the percentages: 67%, 33%, 0%
  2. In H3, enter the formula: =PERCENTILE($D$2:$D$11,G3)
  3. Copy the formula down to rows 4 and 5
  4. Format cells H3:H5 in Wingding3 font
  5. Use Excel's Insert Symbols feature to add the up, right, and down arrows in those cells, from the Wingdings 3 font.
  • NOTE: You could use different percentages, or just type values into H3:H5

Add the Custom Icons

Next, create the icons in column C:

  1. In cell C2 enter the formula that creates the icon:
        =IF(D2="","",IF(D2>=$H$3,$I$3,IF(D2>=$H$4,$I$4,$I$5)))
  2. Copy the formula down to cell C11
  3. Format cells C2:C11 with Wingding3 font, and yellow font color
  4. Select cells C2:C11
  5. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  6. Click Use a Formula to Determine Which Cells to Format
  7. For the formula, enter:   =$B2<$H$4
  8. Click the Format button, and select Green as the font colour, then click OK.
  9. Click New Rule, and click Use a Formula to Determine Which Cells to Format
  10. For the formula,enter:  =$B2>$H$3
  11. Click the Format button, and select Red as the font colour, then click OK.
  12. Click OK
  13. To make the icons appear to be in the same cell as the number, you can create an outside border around the two cells.

Download the Sample File

To download the sample file, please visit the Conditional Formatting Examples page on my Contextures website. This example is in the 2010/2007 version of the download file.

______________

18 comments to Create Your Own Excel Icon Set

  • Whitney Matson

    Thanks! I was looking for a way to do this. One would think in later versions of Excel they will make it so you can insert your own symbols into the icon set tool. Or they should at least add red up arrows and green down arrows. That's the one I have wanted as well.

  • adrienne

    Hi Debra,

    I am trying to create a visual chart showing the number of people who are in particular categories at particular levels. For example, imagine we are looking at a football team and they have six key skills. One of them is kicking which is assessed between 1 and 5. For each skill level, I want to graphically show how many people are in it. I was thinking of having a stick person and if there was one person in a level there would be one stick person, and if there were three people in the level there would be three stick people. Do you know how to get excel to insert an icon/number of icons using a table of data (that will change)as its source?

    Hope this makes sense and I would be so grateful if you can reply as I just can't work out how to do it!

    Many thanks

    Adrienne

  • Mary

    hi,
    I am trying to change the icon color at the conditional formatting but I could not. for example for the traffic light colors (Green, Yellow, Red) instead of those colors I need (Blue, Gray, Red). how can I change the current colors?

  • B's

    You can also click on "Reverse Icon Set" in the management of the conditional formatting.

    Other than that, very helpful to create Icon set that are not available in the default ones.

    Thanks !

  • Chris

    This is really helpful!

    Can you explain what you mean in Step 13 when you say "create an outside border around the two cells."

    • @Chris, if you look at the last picture in the article, in column B, you can see the built-in icons, and the numbers, all in one column.
      Our arrow symbols and numbers are in two separate columns -- C and D. To make them look like they're in the same column, I selected cells C2:D2, then, on the Ribbon's Home tab, I clicked the arrow to see the Border options, then clicked the Outside Borders option. That formatting can be copied down to the last row with data.
      When the gridlines are hidden, the 2 cells with the outside border appear to be one large cell, similar to the built-in icons and numbers.

  • jojofr

    Ow yeah! Thanks dude! :D

  • Elina

    Hi.

    Could you please explain what is written in the formula in the 1st step under - Add the Custom Icons.

    -->
    1.In cell C2 enter the formula that creates the icon:
    =IF(D2="","",IF(D2>=$H$3,$I$3,IF(D2>=$H$4,$I$4,$I$5)))

    I don't understand what should be between "","" . I would appreciate your reply. Many thanks.

    • Amy

      Hi Elina,

      the "","" represents blank values. You would simply put what you want D2 to equal between the 1st set of quotes and if that equals statement is true, what you want the resulting value to be in the 2nd set of quotes.

      An example might be:
      =IF(D2="YES", "GOOD", IF(D2>=$H$3,$I$3,IF(D2>=$H$4,$I$4,$I$5)))

      Or you could put cell references in place of those if you want (as is the case with the nested if functions in this example). If you use cell references, however, lose the quotes.

      Hope that helps!

      Amy

  • anukta c

    Thanks, I was surfing the net for a way to do this. This is very helpful.

  • This was helpful. Thank you.

  • weena roussy

    Hi,
    I was trying to use icons to represent progress for different projects. There are no values to be added. For example "Pre-feasability / Feasability / Conception / Realisation / Completed. I've tried inserting symbols using a drop-down list but I don't have the "symbol" in the drop-down list, I only have it's character which is confusing for the user. Is there a way to go around this?
    Thank-you!
    Weena

  • Florence

    Hello,

    I have the same question as Mary from August 31 2014.

    Is there a way to change the formatted icon color (for the traffic lights, I would like another type of red, green and yellow) ?

    Thank you,

    Florence

  • Brilliant. Lateral thinking around what could have been a tricky problem.

  • Larry

    Is there a way to make the continuum based on positive or negative values instead of a percentile?

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>