I have columns filled with this possible data
- 0
- 1
- na
- blank cells
I want to apply conditional formatting with this rules:
- if cell value = 0 , then the background is red
- if cell value = 1 , then the background is green
Unfortunately if I create the rules for value =0, it is also colouring the ‘blank’ cells red:
My rules look like this:
I have also put special rule just to switch all banks to white but it has no effect.
I have tried to change the rule for the "0" in many ways like checking if the value is between 0 and 0, but still the rule always colours the blanks. How can I create rule which will leave the blanks white and colour only the "0" cells?
I went through couple examples from Stackoverflow which suppose to fix this issue, but I dont know how to apply formulas in generic way for whole columns – I am not working with Excel on daily basis so I dont know how to create advanced formulas.
>Solution :
You can use this formula for the red part:
=(D1=0)*(D1<>"")
It returns true if the cell isn’t empty and the value = 0.
(Btw: like this the condition is international … you could use AND or ISNUMERIC as well – but then in the language of your Excel: =AND(ISNUMBER(D1),D1=0))