Excel formula: Change negative numbers to positive

Generic formula

=ABS(number)

Explanation

To change negative numbers to positive and leave positive numbers along (i.e. get absolute values) use the built-in ABS function. In the example shown the formula in C6 is:

=ABS(B6)

How this formula works

The ABS function is fully automatic. All you need to do is supply a number and ABS will return the absolute value.

Convert negative numbers in place

If you only need to convert negative numbers once, you can convert in-place with Paste Special:

Add -1 to a cell and copy to the clipboard
Select the negative numbers you want to convert
Use Paste Special > Values + Multiply

The video on this page shows this technique and many other paste special shortcuts.

Author

Dave Bruns

Related formulas

Count cells that contain negative numbers

To count the number of cells that contain negative numbers in a range of cells, you can use the COUNTIF function. In the generic form of the formula (above) rng represents a range of cells that contain numbers. In the example, the active cell...

Force negative numbers to zero

To force negative numbers to zero without affecting positive numbers, you can use a formula based on the MAX function. In the example shown, the formula in D5 is: = MAX ( C5 - B5 , 0 ) How this formula works This formula takes advantage of the fact...

Related functions

Excel ABS Function

The Excel ABS function returns the absolute value of a number. Negative numbers are converted to positive numbers, and positive numbers are unaffected.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.