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

Get FREE Advanced Excel Exercises with Solutions!

You may like to keep blank cells instead of zero values in your dataset. There are several ways to do it. But this article will provide you with the easiest method to use a formula to return a blank cell instead of zero in Excel also with 5 alternative methods.


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

To explore the methods, we’ll use the following dataset which represents some salespersons’ sales in two consecutive years. Have a look that there are zero sales of some salespersons. Now we’ll return blank cells for them using the IF and VLOOKUP functions.

Steps:

  • Type the following formula in Cell D14–
=IF(VLOOKUP(B14,B5:D11,3,0)=0,"",VLOOKUP(B14,B5:D11,3,0))
  • Then just hit the Enter button.

You will see that the formula has returned blank cells for zero sales of Oliver.

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

Read More: How to Set Cell to Blank in Formula in Excel 


Formula to Return Blank Cell instead of Zero in Excel: 5 Alternative Methods 

Instead of using a formula, you can easily return a blank cell instead of zero in Excel using some smart alternative methods.

1. Automatically Hide Zero to Return Blank Cell in Excel

In our very first method, we’ll use the automatic operation in Excel that will convert all the zeros to blank cells.

Steps:

  • Click File beside the Home tab.

Automatically Hide Zero to Return Blank Cell in Excel

  • Later, click Option from the bottom section, and a dialog box will open up.

Automatically Hide Zero to Return Blank Cell in Excel

  • Then click the Advanced option.
  • After that select the sheet from the drop-down of Display options for this worksheet section.

Automatically Hide Zero to Return Blank Cell in Excel

  • Finally, just unmark the Show a zero in cells that have zero value option.
  • And press OK.

Soon after you will get blank cells instead of all zeros.

Read More: How to Make Empty Cells Blank in Excel


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

Now we’ll try the Conditional Formatting feature of Excel to do the task.

Steps:

  • Select the data range C5:D11.
  • Then click as follows: Home > Conditional Formatting > Highlight Cells Rules > Equal To.

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

  • Later, type zero in the Format cells that are EQUAL TO box.
  • Select Custom Format from the dropdown list.

Soon after Format Cells dialog box will open up.

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

  • Click the Font option.
  • Choose the white color from the Color section.
  • Then press OK.

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

  • Or click Number > Custom and type three semicolons (;;;) in the Type box.
  • Then press OK and it will take you to the previous dialog box.

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

  • Just press OK.

And yes! All the zero values are now returned with blank cells.


3. Apply Custom Formatting to Return Blank Cell instead of Zero

We can also use custom formatting to return a blank cell instead of zero in Excel. Let’s see how to do it.

Steps:

  • Select the data range.
  • Right-click your mouse and select Format Cells from the Context menu.

Apply Custom Formatting to Return Blank Cell instead of Zero

  • From the Number section click Custom.
  • Later, type 0;-0;;@ in the Type box and press OK.

Apply Custom Formatting to Return Blank Cell instead of Zero

Soon after you will see that Excel has returned blank cells instead of zeros in Excel.


4. Hide Zeros in Excel Pivot Tables to Return Blank Cell

Now we’ll return a blank cell instead of zero in Excel using the Pivot Table.

Steps:

  • Select the whole dataset.
  • Then click: Insert > Pivot Table.

Hide Zeros in Excel Pivot Tables to Return Blank Cell

  • Select your desired worksheet and press OK.

I chose New Worksheet.

Hide Zeros in Excel Pivot Tables to Return Blank Cell

  • Then select the data range from the Pivot Table.
  • After that, click as follows: Home > Conditional Formatting > Highlight Cells Rules > Equal To.

Hide Zeros in Excel Pivot Tables to Return Blank Cell

  • Then type zero in the Format cells that are EQUAL TO box.
  • Select Custom Format from the dropdown list.

Soon after Format Cells dialog box will open up.

Hide Zeros in Excel Pivot Tables to Return Blank Cell

  • Then from the Number section click Custom.
  • Type ;;; in the Type box and press OK.

And we are done.


5. Find and Remove Zeros to Return Blank Cell in Excel

Let’s use the Find and Replace tool in Excel to remove all the zeros from a sheet and return blank cells.

Steps:

  • Select the data range C5:D11.
  • Press Ctrl+H to open the Find and Replace dialog box.
  • Type 0 in the Find what box and keep the Replace with box empty.

Find and Remove Zeros to Return Blank Cell in Excel

Then you will get that 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

We have learned several methods to return blank cells instead of zeros in Excel. Now, if you want to return a dash or specific text instead of zeros then it is also possible in Excel.

Steps:

  • Select the range of data.
  • Right-click your mouse and select Format Cells from the Context menu.

Replace Zeros with Dash or Specific Text

  • Then from the Number section click Custom.
  • Later, type 0;-0;-;@ in the Type box to return dash instead of zeros.
  • Finally, press OK.

Replace Zeros with Dash or Specific Text

Then you will get the output like the image below.

  • To return specific text, just type the text within double quotes replacing dash.

I typed NA.

  • Then press OK.

Now have a look that the cells that are replaced with ‘NA’.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


Conclusion

I hope the procedures described above will be good enough to use a formula to return a blank cell instead of zero in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo