Conditional formatting a "#N/A" value

[Dave1969]

I would like to conditionally format my "#N/A" cells to show red so I can spot values that don't conform to my table...but I can't get the conditional formatting to work.

In the conditional formatting box, I've tried...
#N/A
"#N/A"
'#N/A"

I have it formatted as a text value currently.

Any ideas?

 

[Jonmo1]

You would think the cell showing #N/A would be enough ...

anyway, try

1. Select "Formula Is" in the conditional formatting

=ISNA(A1)

where A1 is the cell you want to test for error.

Hope that helps..

 

[Dave1969]

I tried that but it turns ALL cells red for some reason.

 

[sabrina0034]

Hi Dave, what about using AutoFilter and sifting them out like that?

 

[barry houdini]

I tried that but it turns ALL cells red for some reason.

What formula are you using Exactly? Make sure you use =ISNA(A1) not =ISNA($A$1)

 

[Steeviee]

When doing this kind of conditional formatting, always make sure that you format 1 cell ata time. Therefore do formula is
=Iserror(A1)
as suggested (no $) and inout your formatting.
THEN use the format painter to copy this formatting to the other cells.

Probably not the only way to do but after lots of experience, it's the best way to not get it doing mad stuff.

 

[Dave1969]

What formula are you using Exactly? Make sure you use =ISNA(A1) not =ISNA($A$1)

Yes, I did relative reference it using

=ISNA(O4) in my case

Still seems to affect all cells. Admittedly I've not used conditional formatting in Excel 2007 much, so I could be doing something wrong

 

[Dave1969]

stevie, that fixed it...I needed to use Format Painter to copy the formatting rather than copying the formula down...thanks everyone !!!!

I'm also going to use a countif function to count the exceptions.

Dave