Excel Formula to Return Zero If Negative Value is Found

Suppose we have a dataset of the amounts of two items and the Differences between them. There are some negative numeric values. Let’s change each negative value to zero using formulas and formatting.

Method 1 – Using IF Function

Although the values in the Differences column have been created using a formula, we can wrap the formula inside an IF function to convert the values to zero. The IF function runs a condition over a given statement and returns an output based on the condition.

Steps:

• In cell D5, enter the following formula:

• Press Enter.
• Drag the Fill Handle down to fill all the cells below.

The positive values remain the same but the negative values have been converted into zeros.

Read More: Excel Formula If Cell Contains Negative Number

Method 2 – Using MAX Function

Steps:

• In cell D5 enter the following formula:

The MAX function returns the largest value within a statement.

• Press Enter to return the result.
• Drag the Fill Handle down to fill all the cells below.

The negative values are converted to zero.

Read More: Excel Formula to Return Blank If Cell Value Is Negative

Method 3 – Using Format Cells Feature

In some cases, instead of a formula we can format cells to display negative values as zeroes.

Steps:

• Select the range D5:D14.
• While the cells are selected, press Ctrl+1 to open the Format Cells dialog box.

• In the dialog box select “Custom”.
• In the Type box enter #,##0;”0”.
• Click OK.

• Negative values are converted to zeroes.

Download Practice Workbook

Things to Remember

• The format cells feature will change the format but not the values.

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 Make a Group of Cells Negative in Excel
• How to Count Negative Numbers in Excel

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