Formula to Return Blank Cell instead of Zero in Excel (with 5 Alternatives)

The easiest way to to return a blank cell instead of zero in Excel is to use a formula. In this article we’ll demonstrate and explain that formula, and provide 5 alternative methods.

Formula to Return Blank Cell instead of Zero: Combination of IF and VLOOKUP Functions

The following dataset contains some salespersons’ sales in two consecutive years. There are zero sales in some cells. We’ll use the IFand VLOOKUPfunctions in a formula to display blank cells in those cells instead. Steps:

=IF(VLOOKUP(B14,B5:D11,3,0)=0,"",VLOOKUP(B14,B5:D11,3,0))

The formula returns blank cells for the zero sales of Oliver.

Formula to Return Blank Cell instead of Zero in Excel: Combination of IF and VLOOKUP Functions

Alternative Methods

Method 1 – Automatically Hide Zero

Excel has an built in feature that will convert all the zeros to blank cells automatically.

Steps:

Automatically Hide Zero to Return Blank Cell in Excel

Automatically Hide Zero to Return Blank Cell in Excel

Automatically Hide Zero to Return Blank Cell in Excel

Blank cells will appear where all the zeros were before.

Method 2 – Using Conditional Formatting

Steps:

Use Conditional Formatting to Return Blank Cell instead of Zero in Excel

A Format Cells dialog box will open up.

Use Conditional Formatting to Return Blank Cell instead of Zero in Excel

Use Conditional Formatting to Return Blank Cell instead of Zero in Excel

Use Conditional Formatting to Return Blank Cell instead of Zero in Excel

All the zero values are returned as blank cells.

Method 3 – Apply Custom Formatting

Steps:

<a href=Apply Custom Formatting to Return Blank Cell instead of Zero" width="647" height="637" />

<a href=Apply Custom Formatting to Return Blank Cell instead of Zero" width="528" height="478" />

Blank cells now appear where the zeros were.

Method 4 – Using Pivot Tables

Steps:

Hide Zeros in Excel Pivot Tables to Return Blank Cell

Hide Zeros in Excel Pivot Tables to Return Blank Cell

Hide Zeros in Excel Pivot Tables to Return Blank Cell

A Format Cells dialog box will open up.

Hide Zeros in Excel Pivot Tables to Return Blank Cell

And we are done.

Method 5 – Using Find and Replace

Steps:

Find and Remove Zeros to Return Blank Cell in Excel

All the zeros are replaced with blank cells.

Find and Remove Zeros to Return Blank Cell in Excel

Replace Zeros with Dash or Specific Text

It’s as simple to replace zeros with a dash or specific text instead of blank cells.

Steps:

Replace Zeros with Dash or Specific Text

Replace Zeros with Dash or Specific Text

The output will look like the image below.

I typed NA.

The zeros in the cells now display ‘NA’.

Download Practice Workbook

Formula to Return Blank Cell Instead of Zero.xlsx

Related Articles