Excel Formula to Return Blank If Cell Value Is Negative

Sometimes we use a formula in Microsoft Excel and the calculated result becomes negative, which is unimportant. Instead of showing negative values, we want to display the cell blank. It can be done in 2 ways in Excel. Are you looking for solutions to return blank cells if the cell value is negative? In this article, we’ll discuss how an excel formula return blank if cell value is negative.


Download Practice Workbook

You can download the Excel workbook from here.


2 Suitable Ways to Use Excel Formula to Return Blank If Cell Value Is Negative

There are 2 suitable ways to return blank by excel formula if the cell value is negative in Excel. One is by using the IF function and the another is by using the Format Cells option. In order to describe the methods, we have taken a dataset like the following figure where we have 5 products, their Buying Price and Selling Price. We will calculate the Profit by subtracting the Selling Price from the Buying Price and show a blank cell if the value is negative.

Suitable Ways to Use Excel Formula to Return Blank If Cell Value Is Negative


1. Applying IF function

Applying the IF function is the easiest way to return a blank cell when the formula value is negative. All we need is to use a simple Excel formula using the IF function where we will write the formula in such a way that if the calculated value is negative, it will return a blank cell. In order to apply this method, follow the steps below.

Steps:

  • First, select cell E5 and type the following formula:
=IF(D5-C5<0,"",D5-C5)
  • Then, press Enter and it will calculate the profit. If the profit is positive like the image below, it will display the profit.

Suitable Ways to Use Excel Formula to Return Blank If Cell Value Is Negative

  • Second, select cell E5 and drag the Fill Handle down to the entire column Profit.
  • As a result, it will calculate profits for the 5 products and when the calculated profit is negative, it will show blank cells like the image below.

Read More: Excel Formula to Return Zero If Negative Value is Found


2. Use of Format Cells Option

In order to return blank if cell value is negative, we can also use the Format Cells option. To demonstrate the example, we have taken the previous dataset like the following figure where we have 5 products, their Buying Price and Selling Price. We will calculate the Profit by subtracting the Selling Price from the Buying Price and show a blank cell if the value is negative. Follow the steps below to apply this method.

Steps:

  • To begin with, select cell E5 and type the following formula:
=D5-C5
  • Then, press Enter and it will calculate the profit as shown below.

Suitable Ways to Use Excel Formula to Return Blank If Cell Value Is Negative

  • After that, select cell E5 and drag the Fill Handle down to the entire column Profit.
  • As a result, it will calculate profits for the 5 products like the below one.

Suitable Ways to Use Excel Formula to Return Blank If Cell Value Is Negative

  • Subsequently, select cells (E5:E9) >> Right-click on your mouse >> select Format Cells from the pop-up window.

Suitable Ways to Use Excel Formula to Return Blank If Cell Value Is Negative

  • As a result, the Format Cells window will appear.
  • Then, select Custom from the Category option.
  • Next, type “0;”” “ in the Type box.
  • Then, click on OK.

  • Finally, it will calculate profits for the 5 products and when the calculated profit is negative, it will show blank cells like the image below.

Read More: Excel Formula If Cell Contains Negative Number (6 Examples)


How to Display Blank Cells for Zeroes in Excel

Often we use a formula in Microsoft Excel and the calculated result becomes zero, which is unimportant. Instead of showing zeroes, we want to display the cell bank. Applying the IF function is the easiest way to return a blank cell when the formula value is zero. All we need is to use a simple Excel formula using the IF function where we will write the formula in such a way that if the calculated value is zero, it will return a blank cell. In order to apply this method, follow the steps below.

Steps:

  • Firstly, select cell E5 and type the following formula:
=IF(D5-C5=0,"",D5-C5)
  • Then, press Enter and it will calculate the profit. If the profit is positive like in the image below, it will display the profit.

Suitable Ways to Use Excel Formula to Return Blank If Cell Value Is Negative

  • Secondly, select cell E5 and drag the Fill Handle down to the entire column Profit.
  • As a consequence, it will calculate profits for the 5 products and when the calculated profit is zero, it will show blank cells like the image below.


Things to Remember

  • Using the IF function is the easiest way to return a blank cell if the calculated value from Excel formula is negative.
  • If you use the Format Cells option, your particular format for those cells will be lost and it will display the customized format you have written.

Conclusion

Hence, follow the above-described steps. Thus, you can easily learn how to return blank using an excel formula if the cell value is negative. Hope this will be helpful. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.


Related Articles

Md. Asaduzzaman

Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo