How to Get Blank Cell Instead of Zero Using Formula in Excel

theWe often want to return a blank cell or write some words instead of a zero digit in any cell of Excel. There are several ways to return a blank cell instead of showing the digit zero in Excel, like using formula or by formatting the cells. In this article, we will have a detailed explanation of all the methods that we can use to get a blank cell instead of zero.

📁 Download Excel File

Download the Excel file below.

Learn to Get Blank Cell Instead of Zero Using Formula in Excel with These 6 Approaches

To illustrate things clearly, let us prepare a dataset of different products with their sales worth in the months of November and December. Some of the values in this dataset are 0. Our goal is to get a blank cell here instead of a zero using a formula or by formatting cells in this Excel sheet. The dataset is shown as follows:

The basic Format of the IF Function is as follows:

=IF(The Cell you want to look for a value=”The value”,1,2)

where, 1= Return value if the condition is satisfied and 2= Return value if the condition is not satisfied.

The basic Format of the VLOOKUP Function is explained as follows:

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE). Suppose we want to lookup the cell of products that contain a Refrigerator whose sale amount in December is 0. We need to follow the following steps to blank cells that have the value 0.

⬇️⬇️ STEPS ⬇️⬇️

• We select the cell D15 and write the following formula down

• After that, we press Enter.
• The formula will return blank cells instead of zeros. The result is shown in the following table:

📕 Read More: 4 Easy Ways to Fill Blank Cells in Excel with Formula

Another way to replace zeros with blank cells is to go to Options and change the visibility of zeros for a particular worksheet. The steps for this process are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• First, we click on the File Tab.

• After that, we click on Options.

• Then we go to Advanced Options.
• After that we navigate to Display Options for this Worksheet section, select the Excel sheet named Utilizing Excel Options and uncheck the Show a zero in cells that have zero value option and press OK.

• Finally, we will have blank cells instead of zeros in the places where there were zero values. The result is shown in the following table:

📕 Read More: How to Fill Blank Cells in Excel with Go To Special (4 Easy Ways)

We can format cells conditionally to get our desired results. For doing this, we need to follow these steps:

⬇️⬇️ STEPS ⬇️⬇️

• We select the range of cells C6:D12.
• After that we navigate to conditional formatting by selecting Home >> Conditional Formatting >> Highlight Cell Rules and then select Equal to.

• An Equal To popup box will appear.
• Then we type 0 in the Format cells that are EQUAL TO box and from the right box, we select Custom Format from the drop-down list.

• A Format Cells box will appear. We go to the Font From the Color section, select the white color, and press OK.

• Another way of doing the same thing is by going to the Number We click on the Number section and select the Custom option.
• Then we type three semicolons (;;;) in the Type box and press OK.

• Then we press the OK button again on the Equal To box.

• The zero-value cells will be replaced by blank cells as follows:

📕 Read More: 4 Ways to Leave Blank If Cell Contains Zero with Excel Formula

Custom Formatting can also be employed to cells to replace zeros with blank cells. We can do this by the following process:

⬇️⬇️ STEPS ⬇️⬇️

• First, we select the range of cells.
• Then we right-click our mouse and go to the Format Cells option.

• We go to the Number section and click Custom.
• After that we type 0;-0;;@ in the Type box and press OK.

• The blank cells will appear instead of zeros as shown in the following table:

Pivot table method is another method of replacing cells with zero values with blank ones. For  this we have to follow these steps:

⬇️⬇️ STEPS ⬇️⬇️

• We select the whole dataset and then select Insert >> Pivot Table.

• We can create a separate worksheet for the Pivot Table or we can place it in the same worksheet. Let us select the New Worksheet option.

• We check the column headings on the right named Products, Sales Amount in November and Sales Amount in December.

• A Pivot Table will appear on a separate worksheet. We will now select the data range from the Pivot Table.
• Then we will follow the same steps as that of Conditional Formatting. These include going to Home >> Conditional Formatting >> Highlight Cell Rules >> Equal To…and an Equal To box will appear.

• Then we type 0 in the Format cells that are EQUAL TO box.
• From the right box, we select Custom Format from the drop-down list and a Format Cells box will show up.
• We go to Number >> Custom and type (;;;) in the Type box and press OK.

• We press OK again in the Equal To box.

• The results will be shown in the Pivot Table.

Now we will be exploring an alternative method of replacing zeros with blank cells. This method includes the use of Find and Replace command. The steps for this method are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• First, we select the data range C6:D12.
• Then we go to Find and Select >> Replace or alternatively press Ctrl+H to open the Find and Replace.
• We type 0 in the Find what box and keep the Replace with box empty. We need to take special consideration here. Since our Sales amount values have multiple zeros, so all the zeros will disappear if we just press Replace All.
• To avoid this problem, we check the box Match Entire Cell Contents and press Replace All.

• As a result, all the zeros will be replaced by blank cells as shown in the table below:

Replace Zero with Dash or Specific Text

So far, we have learned to replace zero with a blank cell in Excel using a formula or by formatting a cell. Now, what if we want to write something to the cells that have zero values? For example, we can write Null or put a dash instead in the cells that have zero values. To do such a thing, we must follow these steps:

⬇️⬇️ STEPS ⬇️⬇️

• We select the range of cells and right-click to go to the context menu. From there, we select the Format Cells option.

• We go to the Number section and then click on Custom.
• After that, we type 0;-0;-;@ on the Type box and press OK.

• This will give us a return value of dash (-) instead of a blank cell.

• If we want to write something specific instead of a dash, for example, if we want to write “Null” instead then we can just write the word Null inside the aforementioned formula. We can write this as 0;-0;”Null”;@ in the Type box. After that we press OK.

• By writing this formula we can show the word Null in the cells that have a value of zero. The result will be shown as follows:

📕 Read More: 4 Quick Ways to Insert 0 If Cell Is Blank in Excel

📄 Important Notes

🖊️️  We must take care while replacing a zero with a blank cell by Find and Replace method as one might mistakenly remove a zero from other number digits.

🖊️  Writing any text within the inverted commas in the formula 0;-0;”Null”;@ in the Type box of Format Cells option will display the text written.

📝 Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌  Cells with a value of zero can be replaced by blank cells by formatting the cells to show number in different ways.

📌  We can use Formula involving IF Function and VLOOKUP Function to easily replace zero values with blank cells.

📌  We can show blank cells instead of zeros by changing the display option for separate worksheets.

📌  We can use Find and Replace option to quickly replace zero-value cells with blank cells.

Conclusion

I believe the above-mentioned methods will allow you to get a blank cell instead of a zero either by using formula or by formatting a cell in Excel. This article will hopefully be able to make you understand different ways of doing this simple cell formatting task. If you have any further queries, you can comment below or reach out to me. Don’t forget to visit our ExcelDen page.

Related Articles

• 3 Approaches to Make Empty Cells Blank in Excel
• 10 Quick Tricks to Remove Blank Lines in Excel
• 9 Quick Tricks to Delete Row If Cell is Blank in Excel
• How to Determine and Count If Cell Is Not Blank in Excel