Excel - Conditional Formatting - Cell not blank and equals 0

excel formatting conditional formula
KickAss picture KickAss · Sep 4, 2013 · Viewed 56.3k times · Source

I'm trying to check if a cell is:

  1. blank, and if false (not blank)

  2. If value equals 0. I have tried:

    =IF( NOT( ISBLANK($D:$D) ) & $D:$D=0 ,TRUE,FALSE)

    =IF( AND( NOT( ISBLANK($D:$D) ),$D:$D=0) ,TRUE,FALSE)

I am use Excel 2013 with Conditional Formatting rule: Use a formula to determine which cells to format > Format values where this formula is true

What am I doing wrong? The problem is, the IF($D:$D=0) recognises cells with value 0 and those which are blank as true. I only want it to recognise 0 values. Not blank.

Answer

tigeravatar picture tigeravatar · Sep 4, 2013

I think this is what you're looking for:

=AND(COUNTBLANK($D1)=0,$D1=0)

Note that when you apply it to the whole column, each cell will only look at its respective row. You'll want to keep the reference of $D1 instead of using $D:$D

Related questions

(Excel) Conditional Formatting based on Adjacent Cell Value

I'm trying to apply conditional formatting in Excel on a range of cells, based on the adjacent cell's value, to achieve something like this: The goal is to highlight values in Column B (Actual Expense) red if the value is …

Read More
Conditional formatting color gradient with hard stops

I have a column of data in an Excel sheet which has positive and negative values. What I want to be able to do is apply conditional formatting (a color gradient) from say dark green to light green for positive …

Read More
Excel CSV - Number cell format

I produce a report as an CSV file. When I try to open the file in Excel, it makes an assumption about the data type based on the contents of the cell, and reformats it accordingly. For example, if the …

Read More