MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hidding #N/A, #Value!, ect... Forgot How...

Posted by Tom Woodyard on May 23, 2001 10:57 AM

I used to know a Tip I thought I got from this site but I can't find it. I saw a tip that showed how to hide these error messages in cell until a value was placed in the cell it is refrenceing. Anyone remember how to do this?

Posted by Barrie Davidson on May 23, 2001 11:03 AM

If you are looking to hide all error values, use an if statement. Something like:
=IF(ISERROR("Condition"),"","Value to Return"). If you need any more help let me know.


Posted by IML on May 23, 2001 11:08 AM

If you're look up is dependant on a cell that is to be filled in later, you could also use an if statement. Like if your user is entering something in A1


Barry's is probably better, but this is just the way I normally do it.

good luck

Posted by Aladin Akyurek on May 23, 2001 11:09 AM


It's not a good idea to "suppress" every type of error you can get. I wouldn't recommend hiding/suppressing errors like #DIV/0!, #VALUE!, #NUM!, etc. because these indicate serious problems in the processing involved.

Having said that, you can make use of ISERROR that catches any error:

=IF(ISERROR(f),"",f) where f is the actual formula that does a computation.

If you want to suppress just #N/A which can result from lookup formulas, you can use

=IF(ISNA(lookup-f),"",lookup-f) where lookup-f is the actual lookup formula that you use.


Posted by Tom Woodyard on May 23, 2001 11:14 AM

Got it. Thank you very much!

Posted by Tom Woodyard on May 23, 2001 11:16 AM