XL98: How to Create a Conditional Format to Hide Errors

ID: Q186676

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, you can apply conditional formatting to a cell or a range of cells so that error values are not displayed in the cells.

MORE INFORMATION

In versions of Microsoft Excel earlier than Microsoft Excel 98 Macintosh Edition, you cannot create a custom number format to hide error values that are returned to the cell by the cell formula. For example, if you enter the following in your worksheet:


   A1: =B1/C1   B1: 5   C1: 0

A #DIV/0! error is returned to cell A1.

A new feature in Microsoft Excel 98 called conditional formatting allows you to hide error values that are returned to the cell by the cell formula.

Example

The following example uses conditional formatting to hide error values that are returned by formulas in cells:

Enter the following in a worksheet:
```
      A1: =B1/C1          B1: 5   C1: 0
      A2: =noname*B2      B2: 6   C2: 0 
```
NOTE: Cell A1 returns the #DIV/0! error value, and cell A2 returns the #NAME? error value because "noname" is not a valid defined name.

Select cells A1:A2.

On the Format menu, click Conditional Formatting.

In the Conditional Formatting dialog box, click the Condition 1 list and click "Formula Is."

In the box to the right of the Condition 1 list, enter the following formula:
=ISERROR(A1)

Click the Format button. In the Format Cells dialog box, click the white color in the Color list.

Click OK in the Format Cells dialog box, and then click OK in the Conditional Formatting dialog box.

NOTE: There are other techniques for hiding error values that you can use in versions of Microsoft Excel earlier than Microsoft Excel 98.

For additional information, please see the following article in the Microsoft Knowledge Base:

Q87299 XL: How to Suppress Error Values on a Worksheet

REFERENCES

For more information about conditional number formats, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Microsoft Excel 98 Help, type the following text

formatting cells, conditional formats

and then click Show Topics. Select the "Highlight data that meets conditions you specify" topic, and click Go To. If you are unable to find the information you need, ask the Office Assistant.

Additional query words:

Keywords : xlformat xlformula
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbinfo