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:

**Approach**

**1. Combination of IF and VLOOKUP Functions**

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

`=IF(VLOOKUP(D14,B6:D12,3,0)=0,"",VLOOKUP(D14,B6:D12,3,0))`

**🔨 **Formula Breakdown:

** IF(VLOOKUP(D14,B6:D12,3,0)=0,””,VLOOKUP(D14,B6:D12,3,0))**

- The first part of the formula describes what we want to look at, that is the word Refrigerator in cell
**D14**in this example. - Then separated by a comma, we specify the range of cells where we want to look for it. In this case, it is cells
**B6:D12**. - After that, we specify the column number where the value to return is present. In this case, it is column
**3**of the range of cells selected. - Last of all, we write
**0**to return an exact match. - The
**“”**sign indicates a**blank character**which replaces 0 where there is 0 present as the sales value of the Refrigerator.

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

**Approach**

**2. Utilizing Excel Options**

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)**

**Approach**

**3. Applying Conditional Formatting**

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

**Approach**

**4. Employing Custom Formatting**

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:

**Approach**

**5. Using Pivot 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**.

**Approach**

**6. Applying Find and Replace Command**

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