# 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: 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.

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

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

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

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

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

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

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

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

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.

• Select your desired worksheet and press OK.

I chose New Worksheet.

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

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

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

Then you will get that all the zeros are replaced with blank cells.

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

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

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â€™.

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

## 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

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

Advanced Excel Exercises with Solutions PDF