conditional formattingmicrosoft excelmicrosoft-excel-2013
I am applying a simple conditional format to column of values that formatted as currency. I am trying any cells that are "$0.00".
I have the conditional format for a "Highlight Cell, Equal To"and the value to equal is 0.
This cause any blank cells to become conditionally formatted as well. Is there way to not condition blank cells when looking for a zero value?
#N/A isn't "text" as far as Excel is concerned, it just looks like it. It is actually a very specific error meaning that the value is "Not Available" due to some error during calculation.
You can use ISNA(Range) to match on an error of this type.
Rather than "contains text" you want to create a new blank rule rather than the generic ones and then "Use a formula to determine which cells to format".
In there you should be able to set up the rule for the first cell in your range and it will flow down the rest of the range.
=ISNA(range)
For example, to conditionally format cells B6:B8:
=ISNA($B6).$B6:$B8)
Which will match to true and thus apply the formatting you want.
For reference Microsoft provide a list of the IS Functions which shows what they are as well as examples of their use.
Best Answer
Just create a new rule for blank cells, and put them above the old rule (means it will take priority over your rule).
=ISBLANK(A1). ModifyA1to your top-left cell in range.