Red/Yellow/Green State in LibreOffice Calc

Estimated reading time of this article: 2 minutes

How to show red/yellow/green state in columns for non-empty rows in LibreOffice Calc? E.g. for a todo list?

Value based states can be done using conditional formatting.

States

Content Color Example Interpretation
0 / Empty red 0 to do
0 < x < 1 yellow 0.8 started
>= 1 green 12.10.2020 done
< 0 white -1 ignore,
not relevant

List of states

Example

Red, yellow, green state exampleRed, yellow, green state example

Write something in the column A and the cell next to it will show a red state.

Write the number indicating the state in the cell in column B, e.g. 1 and the cell changes the color.

How to do it?

  1. Create three styles. Menu Styles → New Style… and set the background color according to the name:
    • Green
    • Yellow
    • Red
    • Strikethrough
  2. Open Menu Format → Conditional → Manage…
  3. Click on Add
  4. Add three conditions
    1. Formula is → AND(B1 >= 1, ISNUMBER(B1), NOT(ISBLANK($A1)))
      Apply Style: Green
    2. Formula is → AND(B1 = 0, NOT(ISBLANK($A1)), $A1 <> "")
      Apply Style: Red
    3. Formula is → AND(B1 > 0, B1 < 1, ISNUMBER(B1), NOT(ISBLANK($A1)))
      Apply Style: Yellow
  5. Set the cell range. Set B:B for the whole column B.
  6. Add for column A:A the condition
    Formula is → AND(ISNUMBER(B1), OR(B1 >= 1, B1 < 0)) Apply Style: Strikethrough

Manage Conditional Format DialogManage Conditional Format Dialog

I'm using the colored states for making todo or check lists in LibreOffice Calc. Or simply to show the state of rows like new, in progress or processed.

Version 7 of LibreOffice is now capable of saving it in working xlsx-File for Excel. So, I have saved it for Excel as well.

Files: 

File red_yellow_green_state_example_v14.ods
File red_yellow_green_state_example_v14.xlsx
(modified on ) –