Hide ZERO values in Excel

In this article we will explain how to perform a simple operation that not all users of Excel know how to carry out: to avoid that in our spreadsheets the values zero are displayed.

There are many ways to hide the zero values, depending on the situation, but today we are going to explain the two ways that will solve most of the cases that you will find.

Hide ZERO values in the whole worksheet

Whether what we’re looking for is to hide the ZERO values in all the cells in our spreadsheet, we will have to go to the Excel options located in the tab File and then, in the category Advanced, we have to deactivate the checkbox in “Show a zero in cells that have zero value“.


Keep in mind that we will have to choose the sheet in which the changes are applied through the menu “Show options for this sheet:”.

The result we get will be a spreadsheet in which all of their ZEROvalued cells will be displayed in white.


Hide ZERO values in selected cells

When what we want to do is to hide the zero values only in certain cells, what we will do is to apply a cell format to help us achieve our goal. To do this, the first thing to do is to Select the range of cells for which we want to hide the zero values and open the cell formatting options.


Within the Number tab, select the Custom category and in the Type dialog box you have to enter 0;-0;; @.


Thus, zero values within the selected range will appear as blank cells.

Ocultar_valores_CERO_Excel_Rango_CeldasVacias. gif

In the same way that we have hidden the zero values in a given range showing the cells as empty, we can also highlight those cells with some symbol, for example, a hyphen.

To do this, we will follow the same procedure as the one described to show the empty cells, with the difference that in the Type dialog box we write 0;-0; “-“; @.


The result will be a range of cellsin which cells containing zero valueswill be displayed with a hyphen.

Ocultar_valores_CERO_Excel_Rango_Guion. gif

If you want to hide the ZERO values in an EXCEL PivotTable, visit this link.


If you liked this article, do not forget to share it . If you want, you can download the workbook from this link.