How to use Icon Set Conditional Formatting
We want to represent graphically the position of a value relative to certain thresholds
The▸ menu allows you to set a condition of type ▸ so that you can indicate the thresholds with suitable icons.
The available icon sets are:
- 3, 4 or 5 arrows
- 3, 4 or 5 gray arrows
- 3 flags
- 3 traffic lights 1 and 2 (two different designs)
- 3 symbols (square, triangle, circle)
- 3 smileys
- 3 stars
- 4 circles from red to black
- 4 or 5 ratings
- 5 quarters
- 5 boxes
Conditions for displaying each icon can be specified relative to a value (Value), a percentage of the number of values in the range (Percentage), as a percentage of range values (Percentile[note 1]) or formula (Formula).
The following example illustrates how to implement 3 arrows:
Icons in column B represent the position of the note relative to the rounded average of the column, calculated in B12 with the formula:
For thresholds referring to the result of a cell (and not directly to a value) we use the type Formula:
In column C we represent the evolution of the note in column B relative to the one in column A. Is the note greater than, equal to or less than A?
We introduce in column C a formula calculating the difference between notes. For example:
The result will be negative (the second note is lesser than the first), equal (identical notes), or positive (the second note is greater than the first).
The thresholds may therefore here refer directly to a value and so we use this type:
- A percentile is each of the 99 values which divide the sorted data in 100 equal parts, so that each part represents 1⁄100 of the sample population. A percentile returns the value for a data series running from the smallest to the largest value in a set of data. For Alpha = 25, the percentile means the first quartile = 50 Alpha is the MEDIAN.