Frequently asked questions - Calc

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

Other languages:

How to use Icon Set Conditional Formatting

We want to represent graphically the position of a value relative to certain thresholds

The Format ▸ Conditional ▸ Condition menu allows you to set a condition of type All Cells ▸ Icon Set 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:

Conditional icons: expected result

Icons in column B represent the position of the note relative to the rounded average of the column, calculated in B12 with the formula:

=ROUND(AVERAGE($B$2:$B$10))

For thresholds referring to the result of a cell (and not directly to a value) we use the type Formula:

Conditional icons : thresholds calculated using 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:

=B2-A2

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:

Conditional icons: thresholds with reference to a value

Notes:

↑ 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.

Download an example spreadsheet

[1] 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.

[note 1]