XL97: How to Create a Conditional Format to Hide Errors

Last reviewed: March 13, 1998
Article ID: Q154874
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel, you can create conditional formatting on a cell, or a range of cells, so that error values are not displayed in the cell(s).

MORE INFORMATION

In versions of Microsoft Excel earlier than Excel 97, you could not create a custom number format to hide error values 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

you would get #DIV/0! returned to cell A1. You can change the formula in cell A1 to hide the error value to the following:

   =IF(ISERROR(B1/C1),"",B1/C1)

With the example values given above, this formula returns the empty string ("").

Note, there are other techniques for hiding error values that you can use in versions of Microsoft Excel earlier than Microsoft Excel 97.

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

   ARTICLE-ID: Q87299
   TITLE     : XL: How to Suppress Error Values on a Worksheet

There is a new feature in Microsoft Excel 97 called Conditional Formatting that allows you to use the simpler of the two formulas in cell A1, and still prevent the error value from showing in cell A1.

The following example steps you through the use of Conditional Formatting to hide error values returned by formulas in cells:

  1. Enter the following in a worksheet:

          A1: =B1/C1          B1: 5   C1: 0
          A2: =nofunction*B2  B2: 6   C2: 0
    
       Note: cell A1 returns the #DIV/0! error value, and cell A2 returns
       the #NAME? error value because nofunction (used in cell A2) is not
       a valid function.
    
    

  2. Select column A.

  3. On the Format menu, click Conditional Formatting.

  4. In the Conditional Formatting dialog box, click the Condition 1 drop-down and click "Formula Is".

  5. In the edit box to the right of the Condition 1 drop-down, enter the following formula:

          =ISERROR(A1)
    

  6. Click the Format button. In the Format Cells dialog box, click the Color: drop-down, and click the White square.

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

    NOTE: Because you selected column A in step 2, all the cells in column A were formatted with this Conditional Format. So, any other cells in column A that return error values will not display the error value.

REFERENCES

For more information about Conditional Number Formats, click the Index tab in Microsoft Excel 97 Help, type the following text

   formatting cells, conditional formats

and then double-click the selected text to go to the "Apply conditional formats to cells " topic.


Additional query words:
Keywords : xlformat xlformula kbfaq
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbinfo


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 13, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.