Create Your Own Excel Icon Set

Create Your Own Excel Icon Set

See how to create your own Excel icon set, to overcome a limitation with the built-in options. Icon Sets were added to conditional formatting in Excel 2007, and you can use the icons to highlight the results in a group of cells. This workaround uses symbols on the worksheet, instead of the Icon Set symbols.

Icon Set Example

In this Icon Set 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.

______________

24 thoughts on “Create Your Own Excel Icon Set”

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

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

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

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

  5. This is really helpful!
    Can you explain what you mean in Step 13 when you say “create an outside border around the two cells.”

    1. @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.

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

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

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

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

  9. I am a heavy excel user but never thought of using the conditional formatting custom icon trick…Good stuff and thanks!

  10. I am struggling to apply this example as I think what I require is more simple. I need a red up arrow for any value greater than 1 and a green down arrow for any value less than -1.
    Is anyone able to help with this?

  11. Hi understood the concept, created the custom icon set with its values, how do i apply it to pivot

  12. Can I change the colour to show the pie graph of the icon as 3/4 grey and 1/4 yellow? I am using the pie graph icons in conditional formatting. Thanks.

  13. Can you tell me how I can make an Icon set for Excel from a picture. I have a skunk that I want to use but I don’t know how to make it part of an icon set. The other issue I have is if I try to paste the icon into the spreadsheet it overwrites the numbers and I need it to be next to the numbers.

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.