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.

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

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!

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.

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.

______________