Microsoft KB Archive/87299

= XL: How to Suppress Error Values on a Worksheet =

Article ID: 87299

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q87299



SUMMARY
In Microsoft Excel, you can prevent error values (such as #N/A, #VALUE!, #REF!, and #NUM!) from being displayed.



MORE INFORMATION
To suppress error values, use either of the following methods.

Method 1: Use an IF Statement
Use the following IF statement

=IF(ISERROR,&quot;&quot;, )

where  is the formula for which you want to suppress the error value. If  returns an error, this statement returns &quot;&quot; (a null character); otherwise it returns the result of.

NOTE: There is no space between the quotation marks.

Method 2: Hide the Error Value with Formatting
Use a combination of colors and custom formats to &quot;hide&quot; the error value. To make error values invisible, use the background color for everything in the selected cells except numbers. To do this, follow these steps:  Select the cells you want to format. On the Format menu, click Cells, and then click the Font tab. In the Color list, click the color that matches your background color. Click the Number tab. Create a custom number format that includes a color. For example, create a custom format similar to the following:

[BLACK]General

[BLACK]$#,##0.00;[RED]$#,##0.00

 Click OK.

