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

Example

Red, 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 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.