Home

Why isn't this conditional formatting formula working?

%3CLINGO-SUB%20id%3D%22lingo-sub-842988%22%20slang%3D%22en-US%22%3EWhy%20isn't%20this%20conditional%20formatting%20formula%20working%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842988%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20apply%20conditional%20row%20formatting%20based%20upon%20a%20very%20simple%20formula%3A%20%3D%24J2%3D1%20applied%20to%20%3D%241%3A%241048576.%20The%20J%20column%20contains%20only%204%20values%3A%20blank%2C%200%2C%201%2C%20or%202.%26nbsp%3B%20When%20I%20use%20the%20formula%2C%20some%20(but%20not%20all)%20rows%20with%20a%201%20in%20it%20are%20highlighted.%20However%2C%20some%20(but%20not%20all)%20rows%20that%20contain%20a%200%20or%202%20are%20also%20highlighted%20(see%20attachment).%26nbsp%3B%20I%20am%20using%20Excel%202013.%20I%20would%20greatly%20appreciate%20any%20advice.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20459px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130641i40317D82537E9091%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22excel1.JPG%22%20title%3D%22excel1.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-842988%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843010%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20isn't%20this%20conditional%20formatting%20formula%20working%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843010%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405552%22%20target%3D%22_blank%22%3E%40axis7777%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eno%20attachment.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20the%20conditional%20format%20is%20offset%20by%20one%20row.%26nbsp%3B%20When%20you%20define%20a%20conditional%20format%20with%20a%20formula%20that%20uses%20relative%20reference%2C%20you%20need%20to%20take%20great%20care%20which%20cell%20is%20active%20when%20the%20formula%20is%20defined.%26nbsp%3B%20If%20you%20selected%20the%20whole%20column%2C%20then%20entered%20the%20condition%20as%20...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3D%24J2%3D1%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E...%20then%20in%20row1%20the%20condition%20will%20look%20at%20J2%2C%20in%20row%202%20it%20will%20look%20at%20J3%2C%20etc.%20That%20is%20the%20pattern%20we%20see%20here.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EWith%20the%20whole%20column%20selected%2C%20you%20can%20change%20the%20formula%20to%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3D%24J1%3D1%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EMind%20you%2C%20that%20using%20whole%20columns%20in%20conditional%20formats%20will%20make%20the%20workbook%20slow%2C%20because%20Excel%20will%20have%20to%20evaluate%20over%20a%20million%20rows%20each%20time%20any%20cell%20changes%20in%20the%20spreadsheet.%20It%20would%20be%20better%20to%20apply%20the%20rule%20only%20to%20rows%20with%20data.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EAlso%2C%20you%20can%20avoid%20mistakes%20like%20the%20wrong%20formula%20with%20the%20built-in%20rule%26nbsp%3B%3CSTRONG%3EHighlight%20Cell%20Rules%20%26gt%3B%20Equal%20to%3C%2FSTRONG%3Einstead%20of%20using%20a%20formula.%20The%20rules%20in%20this%20section%20will%20always%20apply%20to%20the%20current%20cell.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20424px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130643iFA6ED5FF2889A2B5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-09-09_10-21-25.png%22%20title%3D%222019-09-09_10-21-25.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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
Highlighted

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

 

 

 

 

 

 

 

Related Conversations
Documentation Migration
SunLeo in Office 365 on
0 Replies
SharePoint 2013 LIst Filters (by latest date)
dmphil in SharePoint on
0 Replies
Cell Validation - Drop Down Lists and Text Formatting
JenSmith in Excel on
1 Replies