27

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?

Format cells that contain the text #N/A

Improve this question
0

6 Answers 6

Reset to default
30

#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.

Red formatting for cells B6:B8 containing #N/A

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.

Improve this answer
5
  • 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.
    – Muzer
    Commented Oct 18, 2017 at 9:14
  • 1
    @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 ♦
    Commented Oct 18, 2017 at 13:19
  • 1
    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 ♦
    Commented Oct 18, 2017 at 13:26
  • @StevenVascellaro note the formula and "applies to" range here: i.sstatic.net/mkk0l.png
    – Mokubai ♦
    Commented Oct 18, 2017 at 13:36
  • Fiddly but it works, thanks!
    – MSC
    Commented Jul 24, 2023 at 0:31
5

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

Improve this answer
4

Use a custom formula of:

=ISERROR($C1)
Improve this answer
1

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.

Improve this answer
1

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

Improve this answer
0

If having more than two #N/A and the rest of the values are having a number or a character, you might solve it by doing this.

First make 1 Rule that tells duplicate Values to become red (or any other prefered colour) Then have a Rule in front or above this one that is having Cell contains and a number or character which is common for all of the cells. Chose a prefered color for this as well. Or make several of the last Rule if you need to define several common characters.

Condition Formatting Rules Manager:

Conditional formatting rules manager

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .