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 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?
- Create three styles. Menu
Styles
→New Style…
and set the background color according to the name:- Green
- Yellow
- Red
- Strikethrough
- Open Menu
Format
→Conditional
→Manage…
- Click on
Add
- Add three conditions
Formula is
→AND(B1 >= 1, ISNUMBER(B1), NOT(ISBLANK($A1)))
Apply Style:Green
Formula is
→AND(B1 = 0, NOT(ISBLANK($A1)), $A1 <> "")
Apply Style:Red
Formula is
→AND(B1 > 0, B1 < 1, ISNUMBER(B1), NOT(ISBLANK($A1)))
Apply Style:Yellow
- Set the cell range. Set
B:B
for the whole column B. - 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.