# Conditional Formatting - ignoring null values

1. ## Conditional Formatting - ignoring null values

I have an =IF statement in column that is set to leave the cell blank if one of the fields is blank, otherwise do a calculation. How do I set the conditional formatting to highlight any cell in the column whether the value of the cell is greater than or less than zero, but no highlighting if the value is zero or null?  Register To Reply

2. ## Re: Conditional Formatting - ignoring null values

For column A try using this formula in conditional formatting

=(A1<>0)*(A1<>"")  Register To Reply

3. ## Re: Conditional Formatting - ignoring null values

Would the following also work?
=and(a1<>0,a1<>"")  Register To Reply

4. ## Re: Conditional Formatting - ignoring null values

After entering the formula above, do I apply another conditional format for values not equal to zero?  Register To Reply

5. ## Re: Conditional Formatting - ignoring null values

Actually, it worked - I just realized I didn't have any format set so everything was "clear". Now it is highlighted.

Thanks, but I'm not sure I understand the logic in the formula - can you explain?  Register To Reply

6. ## Re: Conditional Formatting - ignoring null values

Which formula are you referring to?
Mine simply states that if the value is BOTH not equal (<>) to 0 and not blank, then apply the formatting. The AND function means it needs to meet BOTH criteria.  Register To Reply

7. ## Re: Conditional Formatting - ignoring null values

Your's makes sense (with the "and" statement), it's the other one I'm trying to figure out. Although I applied the one with the multiplication value before I saw your reply.  Register To Reply

8. ## Re: Conditional Formatting - ignoring null values

our formulas return the same thing. remember, in excel: 1=TRUE, and 0=FALSE
so... assuming the value is 5, the following would be calculated using the formula:
=(A1<>0)*(A1<>"")
=(1)*(1)
=1
=true

if the value was blank the formula would calculate the following
=(A1<>0)*(A1<>"")
=(1)*(0)
=0
=false  Register To Reply