Hide zero values in Excel

Problem

We want to hide zero values in Excel.

Solution

Option 1 - A selected range

1. Select the cells that contain the zero values you want to hide and press Ctrl + 1
2. Select Number and then Custom, then type 0;; and press OK.

Opction 2 - A sheet

1. Click File > Options > Advanced.
2. Under Display options for this worksheet, select a worksheet, uncheck the Show a zero in cells that have zero value check box.

Option 3 - VBA: sheets or workbooks.

1. Open the Visual Basic Editor: Alt+F11
2. Copy the following code: una para ocultar y otra para mostrar los ceros

Sub Hide_Zeros()
    ActiveWindow.DisplayZeros = False
End Sub

Sub Display_Zeros()
    ActiveWindow.DisplayZeros = True
End Sub

If we'd like to hide zero values from all sheets:

Sub Hide_Zeros_All_Sheets()
    Worksheets.Select
    ActiveWindow.DisplayZeros = False
End Sub

Sub Display_Zeros_All_Sheets()
    Worksheets.Select
    ActiveWindow.DisplayZeros = True
End Sub

To show all zero values again using the previous code you need to remove the custom format applied in option 1.

Results

Related posts

• Formato
• Formato condicional que diferencia ceros y celdas vacías
• Mostrar u ocultar formas en Excel mediante VBA