# Frequently asked questions - Calc

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

## 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*^{[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)

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^{[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.
- ↑
**R**ed**G**reen**B**lue, abreviated RGB, is a color encoding format. These three colors are the primary colors used especially for display on the screens, and software.