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
axis7777
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

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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies