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)