Excel: How to Keep Certain Values from Being Displayed

• Microsoft Excel for Windows, versions 3.x, 4.x, 5.0, and 5.0c
• Microsoft Excel for Windows 95, 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 ("") 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 "Example 3" later in this
                                  article.

Example 1

To hide numbers only, do the following:

1. 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
   
   

2. Select the range A1:B5.

3. On the Format menu, click Cells (click Number if you are using Microsoft Excel 4.0a or earlier).

4. Select Custom and type "" in the Type box. (Type "" in the Code box if you are using Microsoft Excel 4.0a or earlier.)

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 ";;;" (without the quotation marks) under Type, and click OK.

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),"error",A1/A2)

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

   ARTICLE-ID: Q138080
   TITLE     : XL: Macro to Suppress an Error Value Returned by a Formula