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

Set Cell to Blank in Formula

We’ll use the example dataset, which includes information on Region, Product, Quantity Price, and Sales. There are some blank cells in the Quantity column, which means we receive a zero in the Sales column. We’ll make these cells blank instead.

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

Method 1 – Set a Cell to Blank Using the IF Function

Set Cell to Blank Formula examples

We want to calculate the Sales by multiplying Price and Quantity. When we do so, we get the result as follows. We will calculate Sales and return a blank cell if the cell value is lower than $2,000. Steps:

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

Set Cell to Blank Formula by IF

Set Cell to Blank Formula using IF function

Method 2 – Set a Cell to Blank with IF and ISBLANK

Steps:

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

Set Cell to Blank Formula Isblank

The ISBLANK function will first determine whether the Quantity column has a blank cell. If it does, IF won’t even calculate the value of the cell.

Method 3 – Use the IFERROR Function to Set a Cell to Blank in Excel

We are given Sales and Quantity. We want to determine the price of each product. We can simply divide Sales by Quantity. But when we do so, we get errors since there are empty prices.

Steps:

=IFERROR(D5/E5,"")

Set Cell to Blank Formula IFERROR

Set Cell to Blank Formula uSING iferror

Method 4 – Set a Cell to Blank in Formula Using the ISERROR in IF Function

Steps:

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

Set Cell to Blank Formula USING iserror

Set Cell to Blank Formula IF and ISERROR

Method 5 – Using the IFNA Function to Set a Cell to Blank

We have a data set and want to extract the results of product prices with VLOOKUP. However, not all products exist in the dataset.

Set Cell to Blank Formula ifna

Steps:

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

Formula Explained

Output → 50

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

Set Cell to Blank Formula using IFNA WITH VLOOKUP

Method 6 – Set a Cell to Blank Using the Format Option

Steps:

0;-0;;@

Set Cell to Blank Formula CUSTOM FORMAT

Practice Section

We’ve attached a practice workbook where you can practice these methods.

Set Cell to <a href=Blank Formula PRACTICE HERE" width="487" height="351" />

Download the Practice Workbook

Set Cell to Blank Formulas.xlsx

Related Articles

Save Saved Removed 0 Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel. Read Full Bio

10 Comments

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.

Reply

Abrar Niloy Aug 17, 2022 at 12:56 PM

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 “”.

Reply

Abrar Niloy Aug 17, 2022 at 12:59 PM

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.

Reply

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!

Reply

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 a Helper Column use 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 the Helper Column and Paste them as Values where you want the final output.

Step-03: Press Ctrl + H and the Find and Replace dialog 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”.

Reply

Masum Mahdy Nov 16, 2022 at 4:56 PM

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