In this tutorial, we will walk you through the steps on how to change negative numbers to zero while keeping positive numbers unchanged in Excel. This is a useful trick for manipulating data sets that have both positive and negative numbers while eliminating the negative values and keeping the positive ones intact.
In this example, we have two columns, one for the sales from 2020 and another from 2021. Additionally, we will find the difference in sales between the two years. To do this, we will simply subtract both years from each other.
However, whenever the difference is in the negatives, we want it to show a value of 0. To do this, we will use the MAX function. The MAX function is a built-in function in Excel that returns the maximum value from a range of cells or values.
Here are the steps to follow:
- Select the cell or range of cells that contains the numbers you want to manipulate.
- Type “=MAX(” in the formula bar, and then select the cell or range of cells that you want to change. For example, if you want to change the values in cells A1 to A10, you would type “=MAX(0,”
- Next, type “C2:C13)” to complete the formula. This tells Excel to return the maximum value between zero and the selected range.
- Press Enter to apply the formula.
The negative numbers in the selected range will be changed to zero, while the positive numbers will remain unchanged.
Conclusion
The MAX function is a built-in function in Excel that can be used to accomplish this task quickly and easily. By following the simple steps outlined in this tutorial, you can efficiently manipulate your data in Excel and make it more useful for analysis and reporting. Remember to always double check your formulas and make sure you are applying them to the correct range of cells.
