Hide 0 Value in excel vlookup result

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.

Hey Ram Avtar,

If you want to hide zero values in the result of a VLOOKUP formula in Excel, here are a few easy ways to do that:

1. Using `IF` to hide zero in VLOOKUP

Wrap your VLOOKUP inside an IF formula like this:

This means: If the VLOOKUP result is 0, show a blank ("") instead.
Otherwise, show the actual value.

2. Using Custom Number Formatting

You can format the cell so that zeros are hidden:

Select the cells with VLOOKUP results
Press Ctrl + 1 → Number → Custom
Enter this format:

This hides zeros by displaying them as blank, but zeros still exist in the cell (useful if formulas depend on them).

3. Change Excel setting for the whole worksheet

Go to File > Options > Advanced
Scroll to Display options for this worksheet
Uncheck Show a zero in cells that have zero value

This hides all zero values on the selected worksheet.

Summary:

Method	How it works	Pros	Cons
`IF` formula wrap	Show blank if VLOOKUP = 0	Control at formula level	Formula gets longer
Custom number format `0;-0;; @`	Hide zeros visually	Simple formatting	Zeros exist, may confuse users
Excel option to hide zeros	Applies to entire worksheet	Easy, no formula change	Affects all zeros in sheet

Menu

Ram Avtar Singh