Problem: I applied an icon set. They are adding green checkmarks to cells that are not in the best quality range.
Strategy: By default, Microsoft finds the range of values in your range, divides it by the number of icons in the set (three, four, or five) and creates equal ranges.
- 50-67 gets a red X, 84 and up gets a checkmark.
Excel does a quick calculation to get some icons drawn in. If you have defined limits of acceptable values, you can override the defaults to define your own ranges.
Add the icon set. Select the cells that contain the icon. Go to Home, Conditional Formatting, Manage Rules. Select the one rule and click Edit Rule.
- The default is by percent.
In the sample workbook for this topic, the range of data is 50 to 100. Excel split that range of values into values. Anything of 84 or above gets the green checkmark. Note that because the data is skewed high, 57% of the values in the entire data set are getting green checkmarks.
The Type dropdown in the figure above offers Percent, Percentile, Number, and Formula.
If you would use Percentile, Excel would redefine the ranges. About 40 of the 120 values would get each icon. With this data set, 80-90 gets the yellow marker. 79 and below is red, 91 and above is green.
The Type that I use frequently is Number. When using Number, you can define it so that scores of 95 and above get green, 90-94 are yellow and everything else is failing with red.
- Define the ranges to use.
To use the Formula, you have to type a formula that will result in a number. For example, =AVERAGE($B$2:$M$11)+STDEV.P($B$2:$M$11) will calculate a point that is one standard deviation greater than the mean.