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“.

Hide_ZERO_values_Excel_All_the_Spreadsheet_ExcelOptions

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.

Ocultar_valores_CERO_Excel_Toda_la_hoja

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.

Ocultar_valores_CERO_Excel_Celdas_Formato

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

Ocultar_valores_CERO_Excel_Celdas_Vacias_FormatoCeldas

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; “-“; @.

Ocultar_valores_CERO_Excel_Celdas_guion_FormatoCeldas

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.