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.
How to Use Excel Formula to Return Blank If Cell Value Is Negative: 2 Suitable Ways
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.
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.
- 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.
- 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.
- Subsequently, select cells (E5:E9) >> Right-click on your mouse >> select Format Cells from the pop-up window.
- 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
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.
- 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.
Read More: How to Make a Group of Cells Negative in Excel
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.
Download Practice Workbook
You can download the Excel workbook from here.
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. Don’t forget to drop your comments, suggestions, or queries in the comment section below.
