Conditional formatting

Conditional formatting applies specific formatting, such as a background color or font color, to a range of cells that meet one or more defined criteria, or rules. This allows you to visually highlight important data, patterns, trends, or anomalies.

Nota

When a spreadsheet containing dynamic Odoo data is reopened or refreshed, any conditional formatting that has been defined is updated to reflect any changes to the data.

Different types of conditional formatting are possible:

• Single color: changes the color of the cell’s background or font, or changes the font formatting if cell values meet the defined conditions, e.g., changes a cell to red if an invoice is overdue.

• Color scale: uses color intensity to represent smaller and larger values on a two- or three-color scale, e.g., shows sales performance using increasingly dark tones of the same color.

• Icon set: uses sets of arrows, smileys, or dots in green, orange, and red to visually show how cell values in a range compare to certain defined values, e.g., a green smiley to represent sales performance above a certain amount.

• Data bar: inserts colored bars inside a cell to show how a value compares to the value of other cells in the defined range, e.g., the customer generating the highest sales has the longest bar, with bars of decreasing length for customers generating lower sales.

To add conditional formatting to a range of cells:

1. Select the cells the formatting rule should apply to.

2. Click Format ‣ Conditional formatting from the menu bar.

3. In the Conditional formatting panel that opens to the right of the spreadsheet, click + Add another rule.

   Truco

   Click Add range to select additional ranges of cells if needed.

4. Select the type of Format rules, then enter the information required depending on the rule selected.

Single color

With the range selected and Single color selected as the rule type:

1. Define the conditions to be met for the formatting to be applied by choosing the desired value from the Format cells if … dropdown.

2. Define the Formatting style to be applied by selecting the appropriate font formatting, font color, and/or background color.

3. Haga clic en Guardar.

Example

In the example, single-color conditional formatting changes the background color of cells containing the value FALSE to red, showing at a glance which eLearning courses are not yet published.

Color scale

With the range selected and Color scale selected as the rule type:

1. Define what is used as the Minpoint, i.e., the lowest value, and the MaxPoint, i.e., the highest value, by selecting the appropriate category from the dropdown.

2. Optionally, to create a three-color scale, add a MidPoint, i.e., the middle value.

3. Define the color range by choosing a color for the lowest value, i.e., the Minpoint, and the highest value, i.e., the MaxPoint. For a three-color scale, also choose a color for the MidPoint.

4. Haga clic en Guardar.

Example

In the example, sales performance by salesperson is shown using a two-color scale, with white representing the lowest value, and increasing amounts intensifying in color towards the selected green color.

Adding a Midpoint, with a corresponding color, allows you to highlight performance in relation to a defined target or neutral value, e.g., below or above a target %, or to distinguish between positive or negative values.

Icon set

With the range selected and Icon set selected as the rule type:

1. Click on the set of Icons to use.

2. For the first and second icons:

   • enter the value that represents the lower threshold for the icon

   • indicate whether the cell value must be > (greater than) or ≥ (greater than or equal to) the threshold value

   • define whether the threshold value is a Number, Percentage, Percentile or Formula

   The third icon is added to all cells whose values are below the threshold for the second icon.

   Truco

   Click Reverse icons to reverse the order of the icons.

3. Haga clic en Guardar.

Example

In the example, sales performance by salesperson is shown using a three-scale set of colored dots:

• A green dot represents high performance, and is added to total sales amounts greater than or equal to $50,000.

• An orange dot represents average performance, and is added to total sales amounts greater than $25,000 (but less than $50,000 as this is the lower threshold for the green dot).

• A red dot represents low performance, and is added to all other amounts (i.e., amounts below below $25,000 as this is the lower threshold for the orange dot).

Data bar

Nota

Unlike other conditional formatting rules in Odoo Spreadsheet, this rule allows for the formatting to be applied either on the cells containing the values the rule is based on, or on a corresponding range of cells, e.g., on a column containing text. The Apply to range determines where the formatting is shown, while the Range of values determines which cells the rule is based on.

With Data bar selected as the rule type:

1. Click below Apply to range, then, in the spreadsheet, select the range of cells where the data bars should appear. Click Confirm.

2. Click on the Color dot to select the color of the data bars.

3. Click below Range of values, then, in the spreadsheet, select the range of cells containing the values the rule should be based on. Click Confirm.

4. Haga clic en Guardar.

Example

In the example, the rule is based on the Total sales amounts in column B (with the Range of values being B2:B14), while the formatting is applied on the Salesperson names in column A (with the Apply to range being A2:A14).