Faq/Calc/142

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 :

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 :

In this example, choosing a scale with 3 entries allows us to highlight the intermediate values. We have associated the Yellow color to Percentile 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)

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


 * 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 of the color, proportional to the value in the table.

Formula for the red (R):

$$R=\frac{R_{Max}-R_{min}}{x_{Max}-x_{min}}\cdot(x-x_{min})+R_{min}$$

$$R$$: red component for the x value on the same line

$$R_{Max}$$: red component for the color applied to the maximum in the conditional formatting

$$R_{min}$$: red component for the color applied to the minimum in the conditional formatting

$$x_{Max}$$: maximum value for x in the table

$$x_{min}$$: minimum value for x in the table

Notes :

[[Media:FAQ.CALC 142 ColorScale.ods|Download an example spreadsheet]]