Search code examples
excelexcel-formulaconditional-formatting

Excel Conditional Formatting for cells that re equal to 0 formats also blank cells

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

enter image description here

Unfortunately if I create the rules for value =0, it is also colouring the 'blank' cells red:

enter image description here

My rules look like this:

enter image description here

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))