XL: How to Suppress Error Values on a Worksheet

ID: Q87299

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition
Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows, versions 3.0, 4.0, 5.0
Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.x, 4.0, 5.0
Microsoft Excel for OS/2, versions 3.0

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 the following IF statement


   =IF(ISERROR(<formula>),"",<formula>)

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

Use a combination of colors and custom formats to "hide" the error value. To make error values invisible, use the background color for everything in the selected cells except numbers.

If you are using Microsoft Excel version 5.0 or 7.0, follow these steps:

Select the cells you want to format.

On the Format menu, click Cells. Then, in the Cells dialog box, click the Font tab.

Change the font color to the same color as the sheet background. To do this, click the Color list on the Font tab, and select the background color.

Click the Numbers tab. Create a custom number format that includes a color, as in the following examples:
```
      [BLACK]General
      [BLACK]$#,##0.00;[RED]$#,##0.00 
```

Click OK to return to your spreadsheet.

If you are using Microsoft Excel version 4.0 or earlier, follow these steps:

Select the cells you want to format.

On the Format menu, click Font.

Change the font color to the same color as the sheet background. To do this, click the Color list on the Font tab, and select the background color.

In the Font dialog box, click OK.

On the Format menu, click Number. Create a custom number format that includes a color, as in the following examples:
```
      [BLACK]General
      [BLACK]$#,##0.00;[RED]$#,##0.00 
```

Click OK to return to your worksheet.

REFERENCES

For more information about creating custom number formats in Microsoft Excel version 7.0, click Answer Wizard on the Help menu, and type the following:


   custom number formats

"Microsoft Excel User's Guide 1," version 4.0, pages 220-227
"Microsoft Excel User's Guide," version 3.0, pages 184, 188-193

Additional query words: 1.50 2.20 8.00 xl97 xl98 hiding messages #DIV/0! #NAME? #NULL! clear


Keywords          : kberrmsg xlformula 
Version           : WINDOWS:3.0,4.0,5.0,7.0; MACINTOSH:1.0,2.0,3.0,4.0,5.0,98; os/2:3.0
Platform          : MACINTOSH OS/2 WINDOWS 
Issue type        : kbhowto

Last Reviewed: March 27, 1999