Changing Negative Number to Zero in Excel

This post will guide you how to change negative number to zero in a cell in Excel. How do I make negative numbers to zero with a formula in Excel. How to remove all negative numbers from a range of cells and then changed them to zero value in Excel.

Changing Negative Number to Zero in Excel


If you want to change all negative numbers to zero value from a cell in Excel, you can use a formula based on the MAX function. Like this:

=MAX(B1,0)

changing negative number to 01

The MAX function will check if the value of the Cell B1 is less than 0, it indicated that this value is a negative value. And it will return 0. Otherwise, It will return the value of Cell B1.

You can also use another formula based on the IF function to change all negative numbers to zero in Excel. Just like this:

=IF(B1<0,0,B1)

changing negative number to 02

Related Functions


  • Excel MAX function
    The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Related Posts

Basic Array Formula With Examples

Are you searching for an article for getting the basic array formula with different examples for better understanding? Then congratulations because you have just landed on the right article. In this article, you would get to know the basic array ...

Extract all the matches with helper Column

With Excel's powerful functions IF, INDEX, and MATCH, we can find exactly what you're looking for with a few clicks of the mouse. This step-by-step tutorial will show how easy it is to extract data using these tools and more! ...

How to Find the Earliest and Latest Date in Excel

We have a range of dates and we want to look up the earliest and the latest date based on certain criteria like the earliest date for a showing movie, we can use MIN and MAX functions with IF function ...

How to Create Dynamic Drop Down List without Blank in Excel

This post will guide you how to create dynamic drop down list without blank cells in Microsoft Excel. In Excel, and you can use Data Validation feature to improve the efficiency of data entry in excel, and it also be ...

How to Calculate the Average Excluding the Smallest & Highest Numbers in Excel

Calculating the average for a batch of data is frequently used in our daily life. But for some cases like statistic the average score in a competition, or price analysis, we often calculate the average excluding the smallest and highest ...

Sidebar