Excel-Ur-Data

Looking for how to change negative numbers to positive in Excel is a common task and like every other thing in Excel, there are a number of ways you can do that.

In this post, I will share four methods to achieve that.

Method 1: Multiply by Minus One to Change Negative Numbers to Positive

If you remember basic mathematics calculations, you’ll remember that multiplying two negative numbers will give you a positive number.

So, you can use the same calculation to change a negative number to positive.

To do this, you will just multiply the negative numbers with negative one (-1) and it will return a positive number. That is,

                    = negative_value  *  -1

This will change negative numbers to positive and change positive numbers to negative.

Enter the formula into a cell of your choice and drag to fill in other cells. See below:

Method 2: Change Negative Numbers to Positive Using the ABS Function

The ABS (Absolute) function in excel turns a negative number to a positive number.

It turns any number into an absolute number, by removing any sign attached to it.

The syntax for the ABS function is:

             =ABS(Negative number)

So using this data above, we want to change the negative numbers to positive numbers.

To do this:

• Enter the formula =ABS(C2) and press enter

• Then drag it down to the last value cell

This function works even when you have a mix of both positive and negative numbers.

Method #3:  Negative Numbers to Positive Using Paste Special

Methods 1 and 2 above works great. But it allows us to create new columns of data that contains formulas.

However, in a situation where you want to change the negative numbers to positive numbers while remaining in the same column, then you will have to use the paste special option.

The paste special option has some operation options you can use to perform some simple calculations and you can use one of these options to change a negative number to positive without using any formula or adding an extra column.

To do this, here are the steps to follow:

1. Type -1 into a blank cell in the worksheet

2. Select the cell with -1 and copy it

3. Select the range of cells that contain the negative numbers

4. Right-click -> Paste Special -> Operations -> Multiply

5. Click Ok

Now all the negative numbers have been multiplied by -1. The result is a reversal of signs for each cell value.

Note:

When using this method, note that this is not a dynamic method. So you will need to repeat the steps again anytime you update the data.

Method #4: Use Flash Fill to Remove Negative Sign

The Flash Fill feature in excel is a game-changer and it’s an excellent way to change negative values to positive.

All you need to do is enter the first number as a positive number and excel will recognize the pattern to follow.

To do this, follow these steps:

1. Enter the positive value of the negative value in cell C35 as shown in the video

2. Then select the next cell below, cell C36 and press Ctrl + E.

This will enter all the numbers in positive form.

3. Now, click on the small icon on the right and select “Accept Suggestions”.

Note: This method is also not a dynamic one as it does not update when new data is added. You will have to apply the same process.

I’m sure you found these methods helpful. Do you know any other method for changing negative numbers to positive? Please share in the comment section. I’d love to hear from you.