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 by1
:
=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)
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?
LikeLike
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.
LikeLike