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

Question

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)

Solution 3

Or use the "Format only cells that contain" option and change the first drop down from "Cell Value" to "Errors"

Solution 4

I usually just tricked the system by marking all cells in a specific color and then added a rule to format the "correct" values in black. the result is that all the not correct values remain colored

Solution 5

Another option is to the use the following formula:

=IFERROR(VLOOKUP(A1,[Some Range],[Some Column]), "Not Found")

Replace "Not Found" with some suitable error text.

Then, set a conditional format rule for the error text you've written. If the VLOOKUP finds a value, it'll output the value. Otherwise, it'll output the error text which will then have conditional formatting rules applied to it.

View more solutions

Comments

[Muzer]

One reason using "contains text" wouldn't work is that if you changed your language settings, I suspect "#N/A" might change to whatever the local abbreviation of N/A is.

[Stevoisiak]

When I add the rule, it doesn't flow down to the other cells. It only applies to the cell I've highlighted

[Mokubai]

@StevenVascellaro Make sure you have a range set up, but also make sure that the "$" sign in the formula is in the correct place as it constrains how the rule "flows". To only go down the data it needs to be in front of the column, in my case "B". To go across it needs to be in front of the number but not the column. If it is in front of both then the rule will not work. I have $B6 so that it affects things from row 6 downwards.

[Mokubai]

If you have a multi-column multi-row set of data and want it across everything in your "applies to" range then you just want a start point in that range for the formula and no "$" sign at all.

[Mokubai]

@StevenVascellaro note the formula and "applies to" range here: i.stack.imgur.com/mkk0l.png

[Stevoisiak]

@Mokubai That makes sense. I've added step-by-step directions to your answer in case anyone else has the same issue.

[Mokubai]

@StevenVascellaro Good stuff, I use CF so often that I forget just how bad and counter-intuitive the interface actually is when you're not using the "standard" functions.