Create Coloured Icons

 

Create Coloured Icons

In Excel 2007 and later, you can use icon sets to highlight the results in a group of cells. In Excel 2010 and later, you can customize these sets, but can't change the color of the icons.

If you don't have icons, or want to change the colors, you can use symbols, and a formula in an adjacent cell, to create your own icon sets.

Or, you can use custom Number Formats, as shown in the sample file, on the Color IconsNum sheet.

First, set up the lookup table in cells G3:I5

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

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.