google-sheets

google sheet : Conditional format to match error "#N/A" in a cell

We see #N/A in the cell when we use a formula that returns an error. I want to make a conditional format which detects this error (i.e., if the cell contains #N/A then it will change the cell color).

I tried to use 'Text is exactly' or 'equal to' and use the value #N/A. But it doesn't work.

Is there a way to match this error string (#N/A) ?

Solution

  • In conditional formatting you can use this custom formula

    =isna($C1)

    or

    =iserror($C1)

    This example should work for the range set to C1:C100.

    Change range to suit and see if this helps?