13

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

19

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

  • 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 Oct 18 '17 at 9:14
  • When I add the rule, it doesn't flow down to the other cells. It only applies to the cell I've highlighted – Steven M. Vascellaro Oct 18 '17 at 13:14
  • @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 Oct 18 '17 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 Oct 18 '17 at 13:26
  • @StevenVascellaro note the formula and "applies to" range here: i.stack.imgur.com/mkk0l.png – Mokubai Oct 18 '17 at 13:36
4

Use a custom formula of:

=ISERROR($C1)
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.