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

user_1363960 0
Viewed 3734

We will see "#N/A" in the cell when we use formula that return error . i want to make a conditional format which will detect this error so 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 any other way i can match this error string (#N/A) ?

google-sheets
2 Answers

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?

user_38894230

What worked for me across multiple cells is this custom formula:
=isna(INDIRECT(ADDRESS(ROW(),COLUMN())))
This means "Is the current cell equal to N/A?"

You can also use:
=iserror(INDIRECT(ADDRESS(ROW(),COLUMN())))
This means "Is the current cell an error?"

user_152696540
Related