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")

Or that could be rule formula like

=(A1>5)*ISNUMBER(A1)