Hide 0 Value in excel vlookup result

Ram Avtar Singh (Nagari Sultanpur U.P.Delhi)   (14477 Points)

03 March 2022  

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

  1. Click File > Options > Advanced.

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

  1. Select the cells that contain the zero (0) values that you want to hide.

  2. You can press Ctrl+1, or on the Home tab, click Format > Format Cells.

    Format Cells on the Home tab

  3. Click Number > Custom.

  4. In the Type box, type 0;-0;; @ , and then click OK.