Home

Exceptions within conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-270617%22%20slang%3D%22en-US%22%3EExceptions%20within%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-270617%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20excel%20table%20listing%20various%20data%20formats.%20I%20applied%20conditional%20formatting%20to%20cells%2C%20based%20on%20the%20row%2C%20so%20that%20cells%20that%20are%20greater%20than%20or%20equal%20to%20a%20certain%20value%20are%20highlighted.%20My%20problem%20is%20that%20some%20values%20in%20the%20cells%20are%20entered%20as%20text%20(specifically%20ND%2C%20Non-Detect)%20and%20are%20automatically%20highlighted%20as%20part%20of%20the%20rule.%20I%20have%20tried%20playing%20around%20with%20ISNUMBER%20and%20IF%20functions%20to%20hopefully%20exclude%20these%20cells%20but%20haven't%20had%20any%20luck%20yet.%20Any%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-270617%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-271045%22%20slang%3D%22en-US%22%3ERe%3A%20Exceptions%20within%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271045%22%20slang%3D%22en-US%22%3E%3CP%3EOr%20that%20could%20be%20rule%20formula%20like%3C%2FP%3E%3CPRE%3E%3D(A1%26gt%3B5)*ISNUMBER(A1)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-270621%22%20slang%3D%22en-US%22%3ERe%3A%20Exceptions%20within%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-270621%22%20slang%3D%22en-US%22%3E%3CP%3EOne%20method%20would%20be%20to%20select%20the%20column%20that%20you%20want%20excluded%2C%20and%20create%20a%20new%20rule%20using%20the%20formula%20%3DISTEXT(C3)%2C%20where%20C3%20is%20the%20first%20cell%20of%20your%20selection%2C%20and%20don't%20change%20any%20of%20the%20formatting.%26nbsp%3B%20Then%2C%20in%20the%20Conditional%20Formatting%20Rules%20Manager%2C%20ensure%20that%20it%20is%20listed%20first%2C%20and%20check%20the%20box%20that%20says%20Stop%20If%20True.%26nbsp%3B%20This%20formula%20will%20stop%20for%20any%20text%2C%20but%20if%20you%20want%20it%20to%20not%20highlight%20for%20the%20specific%20terms%2C%26nbsp%3B%20you%20could%20use%20the%20formula%20%3DOR(C3%3D%22ND%22%2CC3%3D%22Non-Detect%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have an excel table listing various data formats. I applied conditional formatting to cells, based on the row, so that cells that are greater than or equal to a certain value are highlighted. My problem is that some values in the cells are entered as text (specifically ND, Non-Detect) and are automatically highlighted as part of the rule. I have tried playing around with ISNUMBER and IF functions to hopefully exclude these cells but haven't had any luck yet. Any suggestions?

2 Replies

One method would be to select the column that you want excluded, and create a new rule using the formula =ISTEXT(C3), where C3 is the first cell of your selection, and don't change any of the formatting.  Then, in the Conditional Formatting Rules Manager, ensure that it is listed first, and check the box that says Stop If True.  This formula will stop for any text, but if you want it to not highlight for the specific terms,  you could use the formula =OR(C3="ND",C3="Non-Detect")

Highlighted

Or that could be rule formula like

=(A1>5)*ISNUMBER(A1)

 

Related Conversations
Documentation Migration
SunLeo in Office 365 on
0 Replies
Cell Validation - Drop Down Lists and Text Formatting
JenSmith in Excel on
1 Replies
conditional formatting with formula
kdwork in Excel on
3 Replies
Relative conditional formatting
melissach in Excel on
8 Replies