Excel Formula to Return Blank If Cell Value Is Negative

There are 2 suitable ways to return a blank if the cell value is negative in an Excel formula: by using the IF function and by using the Format Cells option.

To demonstrate our methods, we’ll use the following dataset. We’ll calculate the Profit by subtracting the Selling Price from the Buying Price, and show a blank cell if the value is negative.

Method 1 – Using the IF Function

Steps:

• Select cell E5 and enter the following formula:

• Press Enter to calculate the profit.

If the profit is a positive value, this value will be displayed.

• Select cell E5 and drag the Fill Handle down to the rest of the cells in the column Profit.

When the calculated profit is negative, blank cells will be shown.

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

Method 2 – Using Format Cells Option

Steps:

• Select cell E5 and enter the following formula:

• Press Enter to calculate the profit.

• Select cell E5 and drag the Fill Handle down the entire column Profit.

Profits for the 5 products will be shown like in the image below

• Select the range E5:E9.
• Right-click on your mouse and select Format Cells from the Context menu.

The Format Cells window will appear.

• Select Custom from the Category option.
• Enter “0;”” “ in the Type box.
• Click on OK.

Now where the calculated profit is negative, blank cells will be displayed.

Read More: Excel Formula If Cell Contains Negative Number

How to Display Blank Cells for Zeros in Excel

The IF function is also the easiest way to return a blank cell when the cell value is zero.

Steps:

• Select cell E5 and enter the following formula:

• Press Enter to calculate the profit.

If the profit is positive, the value will be displayed.

• Select cell E5 and drag the Fill Handle down the entire column Profit.

When the calculated profit is zero, a blank cell will be displayed.

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

Related Articles

• How to Add Negative Numbers in Excel
• How to Show Negative Numbers in Excel
• How to Put a Negative Number in Excel Formula
• How to Count Negative Numbers in Excel

<< Go Back to Negative Numbers in Excel | Number Format | Learn Excel