Display or hide zero values

If your sheet contains zero values or contains calculations that produce zero values, you can hide the values or use formatting options to change how the values are displayed. Excel automatically applies the general or number format to any number you type or paste into a worksheet. These formats automatically remove leading zeros from numbers. If you want to keep leading zeros, you must create a custom number format. For more information about how to keep leading zeros, see Use leading zeros in postal codes and other numbers.

Do any of the following:

Hide all zero values on a sheet

  1. On the Excel menu, click Preferences.

  2. Under Authoring, click View   Excel View Preferences button .

  3. Under Window options, clear the Show zero values check box.

Hide zero values in selected cells

Caution: This option applies only to zero (0) values in cells. If a zero value in one of the selected cells changes to a nonzero value, Excel uses the General number format to display the value.

  1. Select the cells that contain the zero (0) values that you want to hide.

  2. On the Home tab, under Number, on the Number Format pop-up menu  Format Number box , click Custom.

  3. In the Format Cells dialog box, under Category, click Custom.

  4. In the Type box, type 0;-0;;@, and then click OK.

    Note: The hidden values appear only in the formula bar — or in the cell if you edit the contents directly in the cell — and are not printed when you print the sheet. If you are editing directly in the cell, the hidden values are displayed in the cell only while you are editing.

Display hidden zero values in selected cells

  1. Select the cells that contain the hidden zero (0) values that you want to display.

  2. On the Home tab, under Number, on the Number Format pop-up menu  Format Number box , click Custom.

  3. In the Format Cells dialog box, in the Category list, do one of the following:

To

Click

Display hidden values by using the standard number format

General.

Display hidden values by using a date or a time format

Date or Time, and then select the date or time format that you want to use.

Hide zero values that are returned by a formula

  1. Select the cells that contain a zero (0) value.

  2. On the Home tab, under Format, click Conditional Formatting.

    Home tab, Format group

  3. Click New Rule.

  4. On the Style pop-up menu, click Classic, and then on the Format only top or bottom ranked values pop-up menu, click Format only cells that contain.

  5. On the Specific text pop-up menu, click Cell value.

  6. On the between pop-up menu, click equal to, and then in the box next to equal to, type 0.

  7. In the Format with pop-up menu, click custom format, and then on the Color pop-up menu, click White.

Display zero values as blank cells or dashes

You can use the IF function to specify a condition that displays a zero (0) value as a blank cell or as a dash (-) in a cell. To learn more about this function, see IF function.

  1. On a blank sheet, in cell A1 and cell A2, type 10.

  2. In cell A3, type =A1-A2, and then press RETURN .

    The formula returns a zero (0) value.

  3. On the Formulas tab, under Function, click Formula Builder.

    Formulas tab, Function group

  4. In the Formula Builder list, double-click IF.

  5. Under Arguments, click the box next to value1 and type A3.

  6. Under Arguments, click the word True next to is, and then on the pop-up menu, click = (Equal To).

  7. Under Arguments, click the box next to value2 and type 0.

  8. Under Arguments, click the box next to then and do one of the following:

To display zero values as

Type this

Blank cells

""

Dashes

"-"

  1. Click the box next to else and type A3.

  2. Press RETURN .

    The formula in cell A3 returns a zero (0) value, and Excel displays the value located between the quotation marks in the then argument (also called the value_if_true argument).

    Tip: You can put any text that you want to display for zero values in between the quotation marks.

Hide zero values in a PivotTable report

  1. Click the PivotTable report.

  2. On the PivotTable tab, under Data, click Options.

    PivotTable tab, Data group

  3. On the Display tab, do one or more of the following:

To

Do this

Display a certain value in place of an error code

Select the Error values as check box, and then in the box, type the value that you want to display instead of an error code.

To display errors as blank cells, leave the box empty.

Display a certain value in place of an empty cell

Select the Empty cells as check box, and then in the box, type the value that you want to display in empty cells.

To display blank cells, leave the box empty.

To display zeros, clear the Empty cells as check box.

See also

Create and apply a custom number format

Use leading zeros in postal codes and other numbers

Hide error values and error indicators

Display numbers as postal codes, Social Security numbers, or phone numbers

Number format codes

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×