Why isn't this conditional formatting formula working?

Copper Contributor

I am trying to apply conditional row formatting based upon a very simple formula: =$J2=1 applied to =$1:$1048576. The J column contains only 4 values: blank, 0, 1, or 2.  When I use the formula, some (but not all) rows with a 1 in it are highlighted. However, some (but not all) rows that contain a 0 or 2 are also highlighted (see attachment).  I am using Excel 2013. I would greatly appreciate any advice.

excel1.JPG

1 Reply

Hello @axis7777,

 

no attachment. 

 

It looks like the conditional format is offset by one row.  When you define a conditional format with a formula that uses relative reference, you need to take great care which cell is active when the formula is defined.  If you selected the whole column, then entered the condition as ...

 

=$J2=1

 

... then in row1 the condition will look at J2, in row 2 it will look at J3, etc. That is the pattern we see here. 

 

With the whole column selected, you can change the formula to 

 

=$J1=1

 

Mind you, that using whole columns in conditional formats will make the workbook slow, because Excel will have to evaluate over a million rows each time any cell changes in the spreadsheet. It would be better to apply the rule only to rows with data.

 

Also, you can avoid mistakes like the wrong formula with the built-in rule Highlight Cell Rules > Equal to instead of using a formula. The rules in this section will always apply to the current cell.

 

2019-09-09_10-21-25.png