How do I hide #N/A?

**MelbTim** · 05-30-2005, 10:05 PM

Hi,

I have a large spreadsheet with a lot of formulas. A lot of cells end up with the value #N/A. Is there a way to hide that value #N/A (ie so that it looks like there is nothing in the cells)? I hope I'm not displaying my ignorance here. I have a feeling it's possible and probably quite simple.

Thanks

Tim

**dominicb** · 05-30-2005, 11:02 PM

Good morning MelbTim

The standard way of trapping errors using formulae is using the ISERR() function (which doesn't work with the #N/A error) and the ISERROR() function. In general if you have a formula A1/B1 that is causing the error then this formula in it's place would return 0:

=IF(ISERROR(A1/B1),0,A1/B1)

Really, you need to include details of what's causing the error - for example if it's a lookup formula, it may be easier to solve.

HTH

DominicB

**MelbTim** · 05-30-2005, 11:10 PM

Each cell has a VLOOKUP function in it. It automatically takes data from another spreadsheet and when it runs out of data it starts returning #N/A. It's really for neatness of presentation that I want to eliminate it as I typically end up with most of the screen filled with #N/A.

Thanks for any further suggestions you can offer.

Tim

**dominicb** · 05-30-2005, 11:20 PM

Hi MelbTim

Can you give me an example of one of the formulae that returns an error - I need to see how many parameters you're using.

DominicB

**MelbTim** · 05-30-2005, 11:43 PM

Thanks again for your help.

This is what is in cell B12 (other cells have only marginally different formulae):

=VLOOKUP($A12,'[referraldata.xls]autodata(Bei)'!$A$6:$I$10000,2,0)

Cell A12 contains the number 12. The first column of the other spreadsheet (which holds all the source data) doesn't contain the value 12 which generates the #N/A response.

I hope this helps.

Thanks again.

Tim

Bill Martin -- (Remove NOSPAM from address) · 05-31-2005, 12:05 AM

MelbTim wrote:
> Hi,
>
> I have a large spreadsheet with a lot of formulas. A lot of cells end
> up with the value #N/A. Is there a way to hide that value #N/A (ie so
> that it looks like there is nothing in the cells)? I hope I'm not
> displaying my ignorance here. I have a feeling it's possible and
> probably quite simple.
>
> Thanks
>
> Tim

Some people just replace the N/A with 0 or blank as Dominic describes.
Personally I don't like that since it can be misleading in some cases,
but a lot of people do like it.

My solution is generally to just use conditional formatting so that if
#N/A is displayed in a cell the text is formatted as light grey and
perhaps a smaller font than the rest of the page. I can still see that
the cells are N/A, but they're not so distracting as they are in normal
font.

Good luck....

Bill

**mangesh_yadav** · 05-31-2005, 12:06 AM

Hi MelbTim,

When you use VLOOKUP and the search item is not present in the table, it returns #N/A. To avoid this, you could use some thing like:
=IF(ISERR(VLOOKUP($A12,'[referraldata.xls]autodata(Bei)'!$A$6:$I$10000,2,0)),VLOOKUP($A12,'[referraldata.xls]autodata(Bei)'!$A$6:$I$10000,2,0),"")

This will put a blank space in the cell where you now get the #N/A

Mangesh

Bobbie · 05-31-2005, 01:05 AM

OK, so I'll assume that you know why the errors are there, and it is OK
with you for them to be there. If that is the case, and you are just
looking to hide them, a little trick I use is the change the color of
the font for those cells to white. You will still be able to see the
contents of the cell in the function bar when you select one of the
cells with the error in it. But you won't see the errors when you've
selected any other cell on the worksheet or when you print the
worksheet...assuming you use regular, white paper. I use this little
trick when I want top have a side calculation just for my own purposes.

But, as Dominicb said above, it is best to understand what is actually
causing the error and determine how to correct it appropriately.

Maybe this will help..
bb

**MelbTim** · 05-31-2005, 01:26 AM

Thank you all so much for your help. It's very much appreciated.

Cheers

Tim

Peo Sjoblom · 05-31-2005, 02:05 AM

=IF(ISNUMBER(MATCH($A12,'[referraldata.xls]autodata(Bei)'!$A$6:$A$10000,0)),VLOOKUP($A12,'[referraldata.xls]autodata(Bei)'!$A$6:$I$10000,2,0),"")

--
Regards,

Peo Sjoblom

(No private emails please)

"MelbTim" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks again for your help.
>
> This is what is in cell B12 (other cells have only marginally different
> formulae):
>
> =VLOOKUP($A12,'[referraldata.xls]autodata(Bei)'!$A$6:$I$10000,2,0)
>
> Cell A12 contains the number 12. The first column of the other
> spreadsheet (which holds all the source data) doesn't contain the value
> 12 which generates the #N/A response.
>
> I hope this helps.
>
> Thanks again.
>
> Tim
>
>
> --
> MelbTim
> ------------------------------------------------------------------------
> MelbTim's Profile:
> http://www.excelforum.com/member.php...o&userid=23847
> View this thread: http://www.excelforum.com/showthread...hreadid=375130
>

**mpiccione** · 06-23-2005, 11:27 AM

Just use the "ISNA" formula.
It returns TRUE if the value asked is #N/A. So in that case, just ask if it is #N/A and replace the true response for whatever you want.

Hope it helps!

regards

Matías

**db8r** · 07-30-2009, 11:46 PM

Seems like if you have a formula enter "" where there was an #N/A then that cell won't be regarded as blank. This in turn can involve problems when drawing charts. Another reason for changing colour instead. See Excel help HP10070515 for how to do that.