Frequently asked questions - Calc

From The Document Foundation Wiki
< Faq‎ | Calc
Jump to: navigation, search

How to have more than three conditions in a conditional format

Conditional formating (Format ▸ Conditional Formating) allows you to apply predefined styles automatically, when certain conditions are fulfilled. Since version 3.5, the number of conditions is no longer limited.

For earlier versions, you can specify by default up to three conditions for checking the values in cells or formulas. The conditions are numbered 1 to 3. If condition 1 is satisfied, the defined style is used. Otherwise, condition 2 is evaluated and its style is used. If this style does not match, condition 3 is evaluated.

copie d'écran dialogue formatage conditionnel
Formatage conditionnel : jusqu'à trois conditions

Here is a way to set more than 3 conditions.

Using four styles

The conditional format can set up to four different styles using this trick :

  1. Apply one of the four styles to the whole area : this will be the default style.
  2. Use the conditional formatting tool to define the conditions for applying the other three styles.

Using more than four styles

We will use the possibility of defining the format as a function of the result of a formula.

  1. Create as many styles as you require for the formats. In our example, we will create four cell styles, in which the only difference is the color of the background, according to the name of the style : green, yellow, orange and red. We want to conserve the Default style for the cells that do not have a whole value between 1 and 4. The result expected :
    copie d'écran plage contenant de 0 à 4 formatée
    Résultat du formatage conditionnel
  2. Select the area to be formatted. After selection of a range, the first cell selected remains the current cell.
    copie d'écran sélection plage
    Sélection de A1:A5
  3. Go to Format ▸ Conditional Formatting
  4. Check Condition 1 and select Formula is.
    1. In the adjacent area, type the formula :
    STYLE(SI(A1=1;"green";SI(A1=2;"yellow";SI(A1=3;"orange";SI(A1=4;"red";"Default")))))
    

Note :

  • We have selected A1:A5, A1 is the current cell (see above) ; the formula refers only to A1 but the program extends the formula to the other cells selected. Relative references are used (so the second line refers to A2, etc.).
  • The STYLE function is not sensitive to the case in style names. Thus, if the styles red and Red exist, the functions STYLE("Red"), STYLE("RED") or STYLE("red") all apply the same style, depending on the order in which they were created.
  • You must not begin the formula with an equals (=) sign

Formatage conditionnel: classeur exemple