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 Interpretation
0 / Empty red to do
0 < x < 1 yellow started
1 green done
-1 white 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 StylesNew Style… and set the background color according to the name:
    • Green
    • Yellow
    • Red
  2. Open Menu FormatConditionalManage…
  3. Click on Add
  4. Add three conditions
    1. Formula isAND(B1 >= 1, ISNUMBER(B1), ISTEXT($A1))
      Apply Style: Green
    2. Formula isAND(B1 = 0, ISTEXT($A1))
      Apply Style: Red
    3. Formula isAND(B1 > 0, B1 < 1, ISNUMBER(B1), ISTEXT($A1))
      Apply Style: Yellow
  5. Set the cell range. Set B:B for the whole column B.

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.

Files: