How to Display or Hide Zero Values in Excel

This post will guide you how to display or hide zero (0) values in cells in Excel. How do I hide all zero values in a selected range of cells in Microsoft Worksheet.

Display or Hide Zero Values with Format Cells

Assuming that you have a list a data in range A1:C4, which contain numeric values. And you do not want to display zero values in this range of cells. How to achieve it. You can try to Format cells with custom type to achieve the result of displaying or hiding zero values in the selected range. Just do the following steps:

#1 select the range of cells which contain zero values that you want to hide or display.

hide or display zero values1

#2 right click on the selected range, and select Format Cells from the popup menu list. And the Format Cells dialog will open.

hide or display zero values2

#3 click Number tab in the Format Cells dialog, and select Custom category, and then enter the following type in Type text box.


hide or display zero values3

#4 click OK button. You will see that all zero values should be hidden in the selected range.

hide or display zero values4


Display or Hide Zero Values with Excel Options

You can also change the Excel Setting in the Excel Options to Display or Hide All zero values in the current worksheet. Do the following steps:

#1 click File Tab, and select Options. The Excel Options dialog will open.

hide or display zero values5

#2 click Advanced menu in the Excel Options dialog, and scroll down to Display options for this worksheet section, uncheck or check Show a zero in cells that have zero value option. (If unchecked, all zero values would be hidden.)

hide or display zero values6

#3 click OK button.

Video: Display or Hide Zero Values


Leave a Reply