Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a huge Excel sheet where I regularly import mass data into. This data is input to calculations per row. Sometimes, the imported data results in a #NV error in the calculated data.

I would like to be informed when that happens. So I thought, I would use conditional formatting on one single cell if Excel finds #NV in a range which is input for formatting evaluation formula.

Do you know a way to detect #NV in a range via a formula? Seems that isnv() triggers only if all cells in a range are #NV.

I am on Excel 2003.

Edit: adapted title to English

share|improve this question
up vote 0 down vote accepted

As an example, assume the range of cells you want to check is B1:B15. Also, I have assumed that #NV is the German Excel equivalent of #N/A. You can use the COUNTIF function combined with NV() to essentially count how many cells in the range evaluate to #NV.

Enter this in the conditional formatting formula:

=COUNTIF(B1:B15,NV()) > 0

Quite simply, this means that the cell should have the defined formatting applied if there is at least one cell within B1:B15 that is #NV. Note the English equivalent would be:

=COUNTIF(B1:B15,NA()) > 0
share|improve this answer
Perfect, thanks. And: indeed German :-) – Bernd Apr 28 '11 at 14:44

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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