Display or hide zero values

You may have a personal preference to display zero values in a cell, or you may be using a spreadsheet that adheres to a set of format standards that requires you to hide zero values. There are several ways to display or hide zero values.

2016-2013 2010 2007



Sometimes you might not want zero (0) values showing on your worksheets, sometimes you need them to be seen. Whether your format standards or preferences call for zeroes showing or hidden, there are several ways to make it happen.

Hide or display all zero values on a worksheet

1. Click File > Options > Advanced.

2. Under Display options for this worksheet, select a worksheet, and then do one of the following:

   • To display zero (0) values in cells, check the Show a zero in cells that have zero value check box.

   • To display zero (0) values as blank cells, uncheck the Show a zero in cells that have zero value check box.

Hide zero values in selected cells

These steps hide zero values in selected cells by using a number format. The hidden values appear only in the formula bar and are not printed. If the value in one of these cells changes to a nonzero value, the value will be displayed in the cell, and the format of the value will be similar to the general number format.

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

2. You can press Ctrl+1, or on the Home tab, click Format > Format Cells.

   

3. Click Number > Custom.

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

To display hidden values:

1. Select the cells with hidden zeros.

2. You can press Ctrl+1, or on the Home tab, click Format > Format Cells.

3. Click Number > General to apply the default number format, and then click OK.

Hide zero values returned by a formula

1. Select the cell that contains the zero (0) value.

2. On the Home tab, click the arrow next to Conditional Formatting > Highlight Cells Rules Equal To.

3. In the box on the left, type 0.

4. In the box on the right, select Custom Format.

5. In the Format Cells box, click the Font tab.

6. In the Color box, select white, and then click OK.

Display zeros as blanks or dashes

Use the IF function to do this.

Use a formula like this to return a blank cell when the value is zero:

=IF(A2-A3=0,"",A2-A3)

Here's how to read the formula. If 0 is the result of (A2-A3), don't display 0 – display nothing (indicated by double quotes ""). If that's not true, display the result of A2-A3. If you don't want the cells blank but want to display something other than 0, put a dash "-" or other character between the double quotes.

Hide zero values in a PivotTable report

1. Click the PivotTable report.

2. On the Analyze tab, in the PivotTable group, click the arrow next to Options, and then click Options.

3. Click the Layout & Format tab, and then do one or more of the following:

   • Change error display     Check the For error values show check box under Format. In the box, type the value that you want to display instead of errors. To display errors as blank cells, delete any characters in the box.

   • Change empty cell display     Check the For empty cells show check box. In the box, type the value that you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.

Top of Page

Sometimes you might not want zero (0) values showing on your worksheets, sometimes you need them to be seen. Whether your format standards or preferences call for zeroes showing or hidden, there are several ways to make it happen.

Display or hide all zero values on a worksheet

1. Click File > Options > Advanced.

2. Under Display options for this worksheet, select a worksheet, and then do one of the following:

   • To display zero (0) values in cells, select the Show a zero in cells that have zero value check box.

   • To display zero values as blank cells, clear the Show a zero in cells that have zero value check box.

Use a number format to hide zero values in selected cells

Follow this procedure to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the general number format.

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

2. You can use Ctrl+1, or on the Home tab, click Format > Format Cells.

3. In the Category list, click Custom.

4. In the Type box, type 0;-0;;@

Notes: 

• The hidden values appear only in the formula bar — or in the cell if you edit within the cell — and are not printed.

• To display hidden values again, select the cells, and then press Ctrl+1, or on the Home tab, in the Cells group, point to Format, and click Format Cells. In the Category list, click General to apply the default number format. To redisplay a date or a time, select the appropriate date or time format on the Number tab.

Use a conditional format to hide zero values returned by a formula

1. Select the cell that contains the zero (0) value.

2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, point to Highlight Cells Rules, and then click Equal To.

3. In the box on the left, type 0.

4. In the box on the right, select Custom Format.

5. In the Format Cells dialog box, click the Font tab.

6. In the Color box, select white.

Use a formula to display zeros as blanks or dashes

To do this task, use the IF function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.
   Important    Do not select the row or column headers.

   

   Selecting an example from Help

2. Press CTRL+C.

3. Create a blank workbook or worksheet.

4. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.

5. Important For the example to work properly, you must paste it into cell A1 of the worksheet.

6. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab > Formula Auditing > Show Formulas.

7. After you copy the example to a blank worksheet, you can adapt it to suit your needs.

1 2 3 4 5 6 7

A B Data 10 10 Formula Description (Result) =A2-A3 Second number subtracted from the first (0) =IF(A2-A3=0,"",A2-A3) Returns a blank cell when the value is zero (blank cell) =IF(A2-A3=0,"-",A2-A3 Returns a dash when the value is zero (-)

For more information about how to use this function, see IF function.

Hide zero values in a PivotTable report

1. Click the PivotTable report.

2. On the Options tab, in the PivotTable Options group, click the arrow next to Options, and then click Options.

3. Click the Layout & Format tab, and then do one or more of the following:

   Change error display     Select the For error values, show check box under Format. In the box, type the value that you want to display instead of errors. To display errors as blank cells, delete any characters in the box.

   Change empty cell display     Select the For empty cells, show check box. In the box, type the value that you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.

Top of Page

Sometimes you might not want zero (0) values showing on your worksheets, sometimes you need them to be seen. Whether your format standards or preferences call for zeroes showing or hidden, there are several ways to make it happen.

Display or hide all zero values on a worksheet

1. Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.

2. Under Display options for this worksheet, select a worksheet, and then do one of the following:

   • To display zero (0) values in cells, select the Show a zero in cells that have zero value check box.

   • To display zero values as blank cells, clear the Show a zero in cells that have zero value check box.

Use a number format to hide zero values in selected cells

Follow this procedure to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the general number format.

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

2. You can press Ctrl+1, or on the Home tab, in the Cells group, click Format > Format Cells.

3. In the Category list, click Custom.

4. In the Type box, type 0;-0;;@

Notes: 

• The hidden values appear only in the formula bar — or in the cell if you edit within the cell — and are not printed.

• To display hidden values again, select the cells, and then on the Home tab, in the Cells group, point to Format, and click Format Cells. In the Category list, click General to apply the default number format. To redisplay a date or a time, select the appropriate date or time format on the Number tab.

Use a conditional format to hide zero values returned by a formula

1. Select the cell that contains the zero (0) value.

2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting > Highlight Cells Rules > Equal To.

3. In the box on the left, type 0.

4. In the box on the right, select Custom Format.

5. In the Format Cells dialog box, click the Font tab.

6. In the Color box, select white.

Use a formula to display zeros as blanks or dashes

To do this task, use the IF function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important: Do not select the row or column headers.

Selecting an example from Help

1. Press CTRL+C.

2. In Excel, create a blank workbook or worksheet.

3. In the worksheet, select cell A1, and press CTRL+V.

Important: For the example to work properly, you must paste it into cell A1 of the worksheet.

1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab > Formula Auditing group > Show Formulas.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

1 2 3 4 5 6 7

A B Data 10 10 Formula Description (Result) =A2-A3 Second number subtracted from the first (0) =IF(A2-A3=0,"",A2-A3) Returns a blank cell when the value is zero (blank cell) =IF(A2-A3=0,"-",A2-A3 Returns a dash when the value is zero (-)

For more information about how to use this function, see IF function.

Hide zero values in a PivotTable report

1. Click the PivotTable report.

2. On the Options tab, in the PivotTable Options group, click the arrow next to Options, and then click Options.

3. Click the Layout & Format tab, and then do one or more of the following:

   Change error display     Select the For error values, show check box under Format. In the box, type the value that you want to display instead of errors. To display errors as blank cells, delete any characters in the box.

   Change empty cell display     Select the For empty cells, show check box. In the box, type the value that you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.

Did you know?

If you don't have an Office 365 subscription or the latest Office version, you can try it now:

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice

See Also

Overview of formulas in Excel

How to avoid broken formulas

Find and correct errors in formulas

Excel keyboard shortcuts and function keys

Excel functions (alphabetical)

Excel functions (by category)