You are currently offline, waiting for your internet to reconnect

XL: How to Suppress Error Values on a Worksheet

This article was previously published under Q87299
This article has been archived. It is offered "as is" and will no longer be updated.
In Microsoft Excel, you can prevent error values (such as #N/A, #VALUE!,#REF!, and #NUM!) from being displayed.
To suppress error values, use either of the following methods.

Method 1: Use an IF Statement

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

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 "hide" the error value.To make error values invisible, use the background color for everything inthe selected cells except numbers. To do this, follow these steps:
  1. Select the cells you want to format.
  2. On the Format menu, click Cells, and then click the Font tab.
  3. In the Color list, click the color that matches your background color.
  4. Click the Number tab. Create a custom number format that includes a color. For example, create a custom format similar to the following:
  5. Click OK.
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
288972 XL2002: How to Suppress the Printing of Errors in Workbook Cells
XL2002 XL2000 XL97 XL98 hiding messages #DIV/0! #NAME? #NULL! clear

Article ID: 87299 - Last Review: 12/04/2015 09:16:27 - Revision: 3.0

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

  • kbnosurvey kbarchive kberrmsg kbhowto KB87299