## How To Change Number Signs Using Formulas

Today I’m going to have a look at three questions which crop up time and time again on the online forums. The answers are very simple when you know how, so they should be part of every Excel user’s tool belt.

#### How Do I Make All Positive Numbers Negative And All Negative Numbers Positive?

So here’s a short list of numbers where we want to flip the signs. We want `+2` to become `-2`, `-1` to become `+1 `and so on. There are two quick and easy ways to do this with formulas:

• Use the `-` operator to multiply the numbers by `-1`:

`=A2*-1`

• Use the `-` operator to negate the numbers:

`=-A2`

Either way is absolutely fine and they always give the same results, although the computation under the hood slightly differs. Here’s the second one in action:

#### How Do I Make All Numbers Positive?

For this challenge, we want to make all the numbers positive, regardless of their existing sign. One way would be to put together some conditional logic using the `IF()` function:

`=IF(A2<0,-A2,A2)`

So that formula says… if `A2` is less than zero then negate it and give me the result; otherwise just give me the value in `A2`.

However, there is a slightly easier way. Excel has a function called `ABS()` which returns the absolute value of a number: a number without its sign.

`=ABS(A2)`

Let’s put the `ABS()` function into action: #### How Do I Make All Numbers Negative?

The last variation is how to make all numbers negative. This one combines the techniques of the two previous challenges: first use the `ABS()` function to make all the numbers positive and then use the `-` operator to negate them.

`=-ABS(A2)` RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , . Bookmark the permalink.

### 2 Responses to How To Change Number Signs Using Formulas

1. Colin,
Nice post. Another way to change the sign of every number is to type -1 in any cell, then copy it onto the clipboard. Then select the target cells and perform Paste –> Special –> Multiply.
Hope it helps,
Ben a/k/a Excel Esquire
PS: How do you generate these animations?

Like

• Colin Legg says:

Hi Ben,

Yep, that’s a good tip if you want to flip all the signs without using a formula. Thanks for the comment.
I used cropper (which is free) to create the animated gifs on this post. Another piece of software you could use (which is not free) is Camtasia Studio.

Like