Home

Exceptions within conditional formatting

Megan Cooper
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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies