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

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 blank cell instead of zero in Excel also with 5 alternative methods.

Download Practice Workbook

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

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–

• Then just hit the Enter button.

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

5 Alternative Methods to Return Blank Cell Instead of Zero in Excel

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 Find Blank Cells in Excel (8 Easy Ways)

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

Read More: How to Apply Conditional Formatting in Excel If Another Cell Is Blank

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.

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

Read More: How to Set Cell to Blank in Formula in Excel (6 Ways)

Similar Readings

• How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas
• How to Perform If Zero Leave Blank Formula in Excel (3 Methods)
• If Cell is Blank Then Show 0 in Excel (4 Ways)
• How to Find Blank Cells Using VBA in Excel (6 Methods)
• How to Autofill Blank Cells in Excel with Value Above (5 Easy Ways)

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

Related Content: How to Return Value if Cell is Blank (12 Ways)

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.

Read More: How to Find and Replace Blank Cells in Excel (4 Methods)

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 are replaced with ‘NA’.

Read More: Excel VBA: Find the Next Empty Cell in Range (4 Examples)

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

• Fill Blank Cells with Value Above in Excel (4 Methods)
• How to Remove Blank Cells in Excel (10 Easy Ways)
• Null vs Blank in Excel
• VBA to Count Blank Cells in Range in Excel (3 Methods)
• Highlight Blank Cells in Excel (4 Fruitful Ways)
• Find, Count and Apply Formula If a Cell is Not Blank (With Examples)