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