Frequently asked questions - Calc

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

Other languages:

How to apply a Color Scale Conditional Formatting

Applying a color scale to a range means displaying a bicolor or tricolor gradient across the range depending on the value in each cell.

This technique identifies at a glance the distribution or the differences between the values in a range. A typical example might be an array of temperatures, with the lowest colored blue and the highest red, with a gradient for intermediate values.

This layout is conditional, that is to say that the color displayed is dependent on the cell value. You must choose the two "extreme" colors and indicate the method of calculation. The calculation of the color applied will be made in relation to:

Min
Max
Percentile
Value
Percent
Formula

The choices Min and Max are sufficient to themselves as found in the range. Other options need to be specified by a value (Percentile, Value, Percentage) or a cell reference or formula (Formula).

For color scales with 3 entries, you must also inform those choices (color, method of calculation) for the "intermediate" data.

The following example illustrates how to implement a scale with 2 entries via the menu Format ▸ Conditional ▸ Color scale ▸ 2 entries:

Scale from Tango: sky blue 1 to Light red

In this example, we have chosen the color Tango: Sky blue 1 associated to Min (minimum) and the Light red associated to Max (maximum).

The following example illustrates how to implement a scale with 3 entries via the menu Format ▸ Conditional ▸ Color Scale ▸ 3 entries :

Scale from Tango: Sky blue 1 to Light red via Yellow (Centile=50)

In this example, choosing a scale with 3 entries allows us to highlight the intermediate values. We have associated the Yellow color to Percentile^[1] 50, and we have once again associated Tango: Sky blue 1 to Min (minimum) and Light red to Max (maximum).

The following example shows the impact of a change of percentile (here chosen value: 30)

Scale from Tango: Sky blue 1 to Light red via Yellow (Centile=30)

The following example still uses a scale with 3 entries, where the thresholds are derived from values displayed in the spreadsheet:

Scale from Tango: Sky blue 1 to Light red via Yellow (Formule)

The thresholds are displayed in the table in M40, M41, M42.
We use the Formula type to enter the references.
The values used will be respectively 10, 60 and 80. The gradient begins with blue for values equal to 10 (values lesser than 10 will also be colored in blue, the "minimum" color), and ends at 80 in red (values greater than 80 will be colored in red, the maximum value).

Calculation method

It is not necessary to know how LibreOffice computes the gradient in order to use it. The above examples show that it is sufficient in most cases to select the range and choose the "start" and "end" colors to get a result.

For the curious we show below how the program calculates...

The calculated color is a mix of each of the RGB components^[2] of the color, proportional to the value in the table.

Formula for the red (R):

[math]\displaystyle{ R=\frac{R_{Max}-R_{min}}{x_{Max}-x_{min}}\cdot(x-x_{min})+R_{min} }[/math]

[math]\displaystyle{ R }[/math]: red component for the x value on the same line

[math]\displaystyle{ R_{Max} }[/math]: red component for the color applied to the maximum in the conditional formatting

[math]\displaystyle{ R_{min} }[/math]: red component for the color applied to the minimum in the conditional formatting

[math]\displaystyle{ x_{Max} }[/math]: maximum value for x in the table

[math]\displaystyle{ x_{min} }[/math]: minimum value for x in the table

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 return the value for a data series going 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.
↑ Red Green Blue, abreviated RGB, is a color encoding format. These three colors are the primary colors used especially for display on the screens, and software.

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 return the value for a data series going 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.

[2] Red Green Blue, abreviated RGB, is a color encoding format. These three colors are the primary colors used especially for display on the screens, and software.

[1]

[2]