Conditional formatting for #N/A

**kisboros** · 01-16-2012, 01:08 PM

I would like to color all #N/A cells. How to do it? Thank you!

**Special-K** · 01-16-2012, 01:17 PM

Try

=ISNA(A1)

format as required copy the format to other required cells using Format painter.

**kisboros** · 01-16-2012, 02:03 PM

Originally Posted by Special-K

Try

=ISNA(A1)

format as required copy the format to other required cells using Format painter.

What if I want a whole range, like d2:d1112? There are some good numbers in that column, so I cannot copy/paste the conditional formatting rule down because it takes out my good numbers.
Thank you for your help!

**kisboros** · 01-16-2012, 02:32 PM

Thanks for the help!

**dilipandey** · 01-16-2012, 01:20 PM

Hi Kisboros,

Use following formula in conditional formatting -> use a formula to determine which cells to format:-


=ISERROR(D4)

and choose a color (font or background).
where cell d4 is any cell (relative reference) in the range containing #N/As.

If still have issues, post the sample file.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Fotis1991** · 01-16-2012, 01:23 PM

Hi

Conditional Formatting
Highlight the range

Rule =isna($a1)

Hope to helps you

**Tnek** · 01-16-2012, 01:24 PM

You could use conditional formatting... Under Format only cells that contain, there is a selection for Errors. This will pick up all errors, #DIV/0!, #N/A,... etc.

**kisboros** · 01-16-2012, 02:32 PM

Originally Posted by Tnek

You could use conditional formatting... Under Format only cells that contain, there is a selection for Errors. This will pick up all errors, #DIV/0!, #N/A,... etc.

Thanks for the help; this was the easiest!

**dimwit** · 01-31-2018, 07:47 PM

Does this formula no longer work with Excel 2016 ? i used =isna($j3) in the rule for conditional formatting but the cells without #N/A (vlookups that produced results) are getting formatted.

**jeffreybrown** · 01-31-2018, 08:24 PM

Hello dimwit,

I don't have 2016, but I suspect that formula would still work. Anyway, if you have a specific question of your own, please create your own thread.

You can always reference this thread if required.