How do I add conditional formatting to cells containing #N/A in Excel?

I have a column in Excel 2013 filled with values found with VLOOKUP(). For some reason, I am unable to use conditional formatting to highlight cells which contain #N/A.

I tried creating highlighting rules for "Equal To..." and "Text That Contains...", but neither seems to work.

How can I use conditional formatting to highlight cells that contain #N/A?

Solution 1:

#N/A isn't "text" as far as Excel is concerned, it just looks like it. It is actually a very specific error meaning that the value is "Not Available" due to some error during calculation.

You can use ISNA(Range) to match on an error of this type.

Rather than "contains text" you want to create a new blank rule rather than the generic ones and then "Use a formula to determine which cells to format".

In there you should be able to set up the rule for the first cell in your range and it will flow down the rest of the range.

=ISNA(range)

For example, to conditionally format cells B6:B8:

1. Select the first cell you want to highlight. (B6)
2. Click Home -> Conditional Formatting -> Manage Rules -> New Rule.
3. Select Use a formula to determine which cells to format.
4. In the field Format values where this formula is true, enter =ISNA($B6).
5. Click Format to set the cell formatting, then select OK.
6. Click OK again to create the formatting rule.
7. In the Conditional Formatting Rules Manager, edit the range under Applies to (ex: $B6:$B8)
8. Select OK to apply the rule.

Which will match to true and thus apply the formatting you want.

For reference Microsoft provide a list of the IS Functions which shows what they are as well as examples of their use.

Solution 2:

Use a custom formula of:

=ISERROR($C1)