Display or hide zero values
Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007
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.
Newer versionsOffice 2010Office 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
-
Click File > Options > Advanced.
-
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.
-
Select the cells that contain the zero (0) values that you want to hide.
-
You can press Ctrl+1, or on the Home tab, click Format > Format Cells.
-
Click Number > Custom.
-
In the Type box, type 0;-0;; @ , and then click OK.