How to change negative numbers to zeros in Excel

In this tutorial, we’ll show you a tricky trick that can be used to change negative numbers to zeros while leaving positive numbers unchanged.

In the figure below, you see two columns with numbers. To subtract numbers in a column В from the numbers in the column А, follow the instructions.

1. Select cell С1.
2. Enter the following formula:

   =A1-B1

3. Highlight a cell С1, hold its lower right corner and drag the formula down.

   Result:

   

4. To change negative numbers to zeros while leaving positive numbers unchanged, instead of the formula:

   =A1-B1

   use the following:

   =MAX(0,A1-B1)

   =МАКС(0;A1-B1)

   Drag the formula down. We get the result:

   

Explanation: Function MAX (MAX) returns the maximum of two values. If the result of the formula is negative, the function returns 0.