# Conditional formatting thinks formula blank "" is a value?

#### Emerlin

##### Board Regular
Hi, I have a formula where I am checking if a cell is blank = if(a1="","",do math)

I am then applying conditional formatting to cells above a number. Any of the cells that are true or "", get applied that conditional formatting even thought it is blank.

Any ideas? I do not want "0" to show in all these cells.

Thanks.

Last edited:

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### pella88

##### Board Regular
Hi Emerlin,

The issue happens because your formula evaluates as "" in case of false statement, and this is considered as string. During conditional formatting, string is evaluated as higher than number value which returns positive on your conditional formatting.

In order to solve it, the easiest way is to use conditional formatting based on formula, and use following formula (do this for the first cell in your record, and then copy>paste format for all others)

=AND(B9>7,COUNTBLANK(B9)=0)

where B9 is cell with your formula, and 7 is the number which is your reference for conditional formatting. Formula works in way that your cell has to fulfil two criteria: the number has to be greater than defined number AND it cannot be blank > COUNTBLANK returns 1 in case that the cell is blank, including "").

Br
pella88

#### Emerlin

##### Board Regular
Hi Emerlin,

The issue happens because your formula evaluates as "" in case of false statement, and this is considered as string. During conditional formatting, string is evaluated as higher than number value which returns positive on your conditional formatting.

In order to solve it, the easiest way is to use conditional formatting based on formula, and use following formula (do this for the first cell in your record, and then copy>paste format for all others)

=AND(B9>7,COUNTBLANK(B9)=0)

where B9 is cell with your formula, and 7 is the number which is your reference for conditional formatting. Formula works in way that your cell has to fulfil two criteria: the number has to be greater than defined number AND it cannot be blank > COUNTBLANK returns 1 in case that the cell is blank, including "").

Br
pella88
Thank you so much - that was perfect. Your explanation was great!

#### pella88

##### Board Regular
Hi Emerlin,

Just to add one more solution (depending on what you try to achieve, it might also be useful).

In case that you put 0 as result for true statement, but you don't want to see it, you can always set a custom number not to show the resulting zero, for example:0;-0;;@

This will show numbers, but will set empty for 0. In this case, your original conditional formatting will work properly as the result of the condition of formula returns 0 (which is also a number), and the formatting will enable you to hide the 0.

Br
pella88

#### Peter_SSs

##### MrExcel MVP, Moderator
Probably simpler is
=AND(B9>7,B9<>"")

Last edited:

#### Emerlin

##### Board Regular
Thank you to both - these were both great solutions.

#### Peter_SSs

##### MrExcel MVP, Moderator
Thank you to both - these were both great solutions.
Glad you got a successful outcome.