Excel: How to Keep Certain Values from Being Displayed

ID: Q25903

The information in this article applies to:

Microsoft Excel for Windows, versions 3.x, 4.x, 5.0, 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:

Select the cells that contain the values.

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

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.

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 "" in the Type box. (Type "" 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:

Select the cells.

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

Select Custom under Category, and enter ";;;" (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),"error",A1/A2)

When you use the ";;;" number format in cell A3, the result of the IF() function (the text string "error") 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 :