Microsoft KB Archive/25903

{|
 * width="100%"|

Excel: How to Keep Certain Values from Being Displayed

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.x, 4.x, 5.0, 5.0c
 * Microsoft Excel for Windows, version 7.0
 * Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0, 5.0, 5.0a

-

SUMMARY
In Microsoft Excel for the Macintosh, if you do not want certain values to be displayed or printed, do the following:


 * 1) Select the cells that contain the values.
 * 2) On the Format menu, click Cells (click Number if you are using Microsoft Excel 4.0a or earlier).
 * 3) Click the Number tab, select Custom, type two quotation marks (&quot;&quot;) and click OK. (Type the quotation marks in the Code box if you are using Microsoft Excel 4.0a or earlier.)

MORE INFORMATION
The following table shows the custom formats you can use to keep certain values from being displayed.

  To hide this                   Use this format

Positive numbers              ;-General;General;General

Negative numbers              General;;General;General

Zero values                   General;-General;;General

Text values                   General;-General;General;

All values                    ;;;

Error values                  See &quot;Example 3&quot; later in this article.

NOTE: Variations of these formats may also be used instead of General.

Example 1
To hide numbers only, do the following:

  In a new worksheet, enter the following sample data:

     A1:  Monday         B1:   1500 A2: Tuesday        B2:   2500 A3: Wednesday      B3:   3500 A4: Thursday       B4:   4500 A5: Friday         B5:   5500  Select the range A1:B5. On the Format menu, click Cells (click Number if you are using Microsoft Excel 4.0a or earlier). Select Custom and type &quot;&quot; in the Type box. (Type &quot;&quot; in the Code box if you are using Microsoft Excel 4.0a or earlier.)

Note that the values in the range B1:B5 are no longer visible on your worksheet. However, if you select any cell in the range B1:B5, its value will be displayed in the formula bar.

Example 2
To hide ALL cell entries, do the following:


 * 1) Select the cells.
 * 2) On the Format menu, click Cells, and click the Number tab. (Click Number if you are using Microsoft Excel 4.0a or earlier.)
 * 3) Select Custom under Category, and enter &quot;;;;&quot; (without the quotation marks) under Type, and click OK.

NOTE: This method will not hide error values. To hide an error value, see Example 3.

Example 3
To hide an error value, use the ISERROR function to trap the error value, and display a text value instead, as in the following example:

  A1: 5 A2: 0 A3: =IF(ISERROR(A1/A2),&quot;error&quot;,A1/A2)

When you use the &quot;;;;&quot; number format in cell A3, the result of the IF function (the text string &quot;error&quot;) will be hidden in cell A3.

For an example of how to do this with a macro, please see the following article in the Microsoft Knowledge Base:

Q138080 : XL: Macro to Suppress an Error Value Returned by a Formula

Additional query words: howto

Keywords :

Version :

Platform :

Issue type :

Technology :