How to get conditional formatting to ignore #DIV/0 cells

[juntjoo]

I've a column I'm applying this to, a 3 color gradient format which works unless cells that contain the div error are present, then the formatting doesn't work at all no where. So how would you get it to ignore those cells? The formatting rule is based on numbers. Do I need to if a if() statement in there to exclude non-numeric values? Thanks

[JohnTopley]

Try

CF with


=isnumber(A1)

[juntjoo]

Try

CF with


=isnumber(A1)

So I tried your suggestion and excel told me no relative references with CF. Any work around? Thanks

[sandy666]

• select your range
• add new rule in CF =NOT(ISNUMBER(A1))
• select font format color white (or cell background black, whatever you want)
• ok

A1 is example here, add your original first cell in range

or try: ISERROR(A1) , ISERR(A1)