losafinders.blogg.se

To make the icons appear to be in the same cell as the number, you can create an outside border around the two cells.Click the Format button, and select Red as the font colour, then click OK.Click Use a Formula to Determine Which Cells to Format.On the Ribbon’s Home tab, click Conditional Formatting, then click New Rule.Format cells C2:C11 with Wingding3 font, and yellow font color.In cell C2 enter the formula that creates the icon:.NOTE: You could use different percentages, or just type values into H3:H5.Use Excel’s Insert Symbols feature to add the up, right, and down arrows in those cells, from the Wingdings 3 font.In H3, enter the formula: =PERCENTILE($D$2:$D$11,G3).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. In the screen shot below, column B shows the built-in icons, for numbers 10 to 100. Then, add a formula and formatting in an adjacent cell, to show the applicable icon. Set up a lookup table with the values and symbols for your formatting rules. If you can’t find the icons that you need, you can create your own set. If you want a red Up Arrow, instead of green, you’re out of luck! Unfortunately, you can’t change the color of the 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. This feature was improved in Excel 2010, and now you can customize these sets, creating your own mix from the existing icons. In this Icon Set example, higher sales numbers show a green up arrow.

This workaround uses symbols on the worksheet, instead of the Icon Set symbols. 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. See how to create your own Excel icon set, to overcome a limitation with the built-in options.