When performing calculations in Excel, sometimes we want the results to be displayed as a blank cell if the computation does not meet our needs. When we use Excel formulas, it frequently returns a zero result if there are blank cells or if the calculation yields a blank cell. We’ll look at a few different ways in Excel to set a cell to blank in a formula. We’ll use the example dataset, which includes information on Region, Product, Quantity Price, and Sales.

**Table of Contents**hide

## How to Set Cell to Blank in Formula in Excel: 6 Ways

As you can see, there are some blank cells in the **Quantity **column in our example data set, therefore when we calculate, we receive zero in the **Sales **column. We’ll look at how to utilize formulas to make these cells blank.

### Method 1: Set Cell to Blank Using IF Function

Here, we want to calculate the **Sales **by multiplying **Price **and **Quantity**. When we do so, we get the result as follows.

Now, we will calculate **Sales **and want to return a blank cell if the cell value is smaller than the amount **$2000**.

**Steps:**

- First, click on cell
**F5**and type the following formula.

`=IF((D5*E5)>2000,D5*E5,"")`

- Now, press the
**ENTER**.

- Finally, drag down using the mouse right key to AutoFill the rest of the series.

.

Here, using **the IF function** we are telling Excel to return the value of the multiplication of **Quantity*Price** if it is **greater **than **$2000** otherwise return a blank cell.

**Read More:** Formula to Return Blank Cell instead of Zero in Excel

### Method 2: Set Cell to Blank by IF along with ISBLANK

We can use a combination of **IF** and **ISBLANK** also to get our desired result. Let’s get into this.

**Steps:**

- First, click on cell
**F5**and type the following formula.

`=IF(ISBLANK(D5),"",D5*E5)`

- Now, press the
**ENTER**.

- Finally, drag down to
**AutoFill**for the rest of the series.

**ISBLANK **function will first determine whether the **Quantity **column has a blank cell, if yes then it will return the result as a blank cell otherwise calculate **D5*E5**.

### Method 3: IFERROR Function to Set Cell to Blank in Excel

**The IFERROR function** helps to catch mistakes (if any) in Excel and replace them with a blank cell, another value, or a custom message. Here, we are given **Sales **and **Quantity**. We want to determine the **price **of each product. So, we can simply divide **Sales **by **Quantity**. But when we do so, we get **errors**.

**Steps:**

- First, click on cell
**F5**and type the following formula.

`=IFERROR(D5/E5,"")`

- Now, press the
**ENTER**.

- After that, drag down to
**AutoFill**rest of the series.

That’s it.

The **IFERROR **function is replacing all the **error **values with a **blank cell** here.

### Method 4: Set Cell to Blank in Formula Using ISERROR Function

**the ISERROR function** is another useful function that can be a solution to our problem. Earlier we used the **IFERROR** function, **I****SERROR** along with the **IF** function does the same task. We’ll explore that in this section. Let’s see how to use it.

**Steps:**

- First Type the following formula in Cell
**F5**.

`=IF(ISERROR(D5/E5),"",D5/E5)`

- Now, press the
**ENTER**.

- Finally, drag down to
**AutoFill**the rest of the series.

**ISERROR **function helps us to determine whether a calculation is accurate or not, **IF** along with **ISERROR **will help us to yield **blank cells** if the cell is in error mode.

### Method 5: IFNA Function to Set Cell to Blank

Now, we will see the use of **the IFNA function** function for generating blank cells.

As you can see, we have a data set and from that table, we want to extract the results of product prices. We will use a combination of **VLOOKUP **and **IFNA **in this case.

If you want to know more about **VLOOKUP**, check this out in this article **How to Use VLOOKUP Function****.**

**Steps:**

- First, type the following formula in cell
**G5**.

`=IFNA(VLOOKUP(F5,$B$4:$D$12,3,FALSE),"")`

**Formula Explained**

**VLOOKUP(F5,$B$4:$D$12,3,FALSE) →**returns the price of Mouse (value of**F5**) from the 3rd column of the**B4:D12**range.

** Output** → 50

**IFNA(VLOOKUP(F5,$B$4:$D$12,3,FALSE),””) →**becomes**IFNA(50,””)**

** Output → **50 (since the value was not N/A)

- Now, press the
**ENTER**.

- At last, drag down to
**AutoFill**the series.

As you can see, **Mouse **and its price are available in the data table, and **VLOOKUP **returned the price. But **Tablet **and **Shirt **are missing in the dataset that’s why **VLOOKUP **doesn’t find and it is not applicable for the dataset, that’s why the **IFNA **function helped to return the value as a blank cell, and as the **Controller **is present in the dataset it returned the value as **0**.

There is a function called **ISNA** you can combine with **IF**, which will perform the same as the **IFNA**.

### Method 6: Set Cell to Blank Using Format Option

So far we’ve shown you setting blank cells within the formula, you can also do that differently rather than the conventional formula. At the end of this article, we will see the use of the custom format option to generate blank cells.

**Steps:**

- Select the desired range you want to format and go to the
**Home**tab and click on the**small arrow**as shown in the image or press**CTRL+1**.

- Now, a dialogue box will pop up, and type the following formula in the
**Type**bar.

`0;-0;;@`

- At last, click
**OK**.

That’s all.

## Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.

**Download Practice Workbook**

## Conclusion

These are 6 different methods for using Excel Set Cell to Blank in Formula. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback

The entire premise of this article is a lie. At no point do you insert a blank cell or blank out a cell. You are inserting an empty string into those cells. Which means that the exported values from those excel sheets will include entire rows of those empty cells, which is not desired. As far as I’m aware, Excel offers now way to actually blank out a cell and it is a huge issue for me.

Hi Jeff, so sorry for your unfortunate situation. But the formulas are structured in such ways that it returns empty strings if only certain criteria are met. It won’t result in entire rows or columns being empty.

Agree with Jeff, none of these options make the cell a properly blank cell. Easily verifiable by using a formula like =COUNTA(range). This will also count the cells made ‘blank’ by inserting the empty string “”.

Hi Eric, the COUNTA function counts all the cells including empty strings. It ignores a cell only if there was no value entered or you have deleted it completely (pressing delete or erasing the entire cell value).

But if you use the COUNTBLANK function to count the same range, you will find that the empty strings will indeed are being counted as blank values.

Excel is weird.

| A | B |

1 | =”” | =ISBLANK(A1) |

2 | | =COUNTBLANK(A1:A2) |

Try this and see what you get: apparently A1 is NOT blank, but it counts as blank!

I agree with the other commenters — the lack of a way to programmatically set a blank value has caused me much extra work over the years.

The only way I have discovered to actually insert a blank value is to use one of the formulas the author proposes (that is, the result is an empty string – “”), BUT IN AN ADJACENT COLUMN to where I want the final value. Then, copy the results of that column, and “Paste Special -> Values” (that is, right click and choose Paste Special, then select Values) into the column where I need actual blank cells. So far, this has worked.

Hello DQNOK,

Thank you for your feedback.

I agree with all the other commenters. If you try to use any of these cells that you set to “blank” in a formula where you check to see if the cell is blank or not and they all evaluate to NOT BLANK!

Hi Penny,

If you check to see if the cells are blank or not then they will be evaluated as not blank because the cells contain formula. If you need blank cells then you can follow these steps.

Step-01:In aHelper Columnuse any of the formulas from this article. But instead of an empty string (`""`

) write a letter or a special character (`"#"`

) in the formula.`=IF((D5*E5)>2000,D5*E5,”#”)`

Step-02:Copy the values from theHelper Columnand Paste them asValueswhere you want the final output.Step-03:PressCtrl+Hand theFind and Replacedialog box will appear. Replace#with nothing and you will get your blank cells.Finally, if you check the cells, then you will see the cells are blank.

I have shared the necessary images for your convenience. I hope this will solve your problem. Please let me know if you have other queries.

Thanks!

Agree with all the others – the lack of a way to set a cell value to TRULY blank is a major pain. Leads to workarounds like those stated – i.e., copy and “Paste Values” – manual steps that you have to repeat every time, e.g., you update a live data source into a worksheet. Major pain. Author keeps trying to dance around it , but at the end of the day, there is no programmatic way to set a cell value to “blank”.

Hi JIM, we are really sorry if these methods cannot help you. Yes, we agree with all of you that empty strings and blank cells are not the same in Excel. We have tried to show some ways to set cells to “seemingly blank” using

Excel formulas.Now, if you need to set cells to absolutely empty (nothing inside cells), sorry Excel formula cannot help you in this regard. But if seemingly blank is enough for you, these formulas work properly.

If you have a specific case, where you need to set cells to truly blank, then let us know at [email protected] with your problem-details and Excel file. We will definitely try to help.

Best regards.

-ExcelDemy Team