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

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.

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.

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

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

• 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, ISERROR 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.

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

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

• 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

Related Articles

• How to Find Blank Cells in Excel
• Null vs Blank in Excel
• How to Highlight Blank Cells in Excel
• How to Make Empty Cells Blank in Excel
• How to Deal with Blank Cells That Are Not Really Blank in Excel
• Return Non Blank Cells from a Range in Excel

<< Go Back to Blank Cells | Excel Cells | Learn Excel