Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
10000+ Copies Already Downloaded
Last week, I got a mail from one of my subscribers with a question.
Hey Puneet, how many methods we have to convert a negative number into a positive?
You know, the thing for which he asked is a common kind of task. I am sure, this often happens with you, when you get some numeric values which are in negative, and after that, you convert them into positive values.
There is no rocket science in this.
But have you ever checked how my different methods you have to do this?
Well, I am alway
And, you will be amazed that I have got 7 different ways which you can use to deal with negative numbers. So, today in this post, I will share these 7 methods with you.
A simple calculation.
Unlike me, if you are good at maths, I am sure you know that when you multiply two minus signs with each other, the result is always positive.
So, you can use the same method in excel to convert a negative number into positive. All you have to do just multiply a negative value with -1 and it will return the positive number instead of negative.
Below you have a range of cells with negative numbers. So, to con
This method is simple, easy to apply, and the best part is it's just normal calculation, no complex formula.
A Specific Function
Turning a negative number into positive is quite easy with ABS. This function is specifically for this task.
It can convert any number into an absolute number. In simple words, it will return a number after removing its sign.
You just have to refer a negative number into the function and it will turn it into a positive value.
This formula will simply return positive numbers.
A different way.
Let’s think about a different situation where instead of getting positive numbers in a different column you need them in the same column.
And for this, you can use paste special option. Wondering how? Let me tell you.
In paste special option, there are “operation” options which you can use to perform some simple calculations. And, you can use these same options to make negative numbers positive without using any formula or adding any extra column.
Just follow these steps.
Now, all the negative number are converted into positive.
The only thing you need to take care is that this is not a dynamic method. So, you need to do it again and again if you frequently update your data.
But the thing is, this method is quick and easy to use, and you don’t need any formula.
This method is insane.
I am sure you have used flash fill once in your lifetime. And if not, you must use it, it’s a game change. This is an insane method to turn negative numbers into positive.
Here are the steps.
Congratulation, you have converted all the negative numbers into positive with flash fill. This method is also not a dynamic one, but you can just use the shortcut key every time when you need to do it.
Simply show negative number into positive numbers.
This is also a possibility that instead of converting a negative number you just want to show it as a positive number. And, in this situation, you can use custom formatting.
Custom formatting can help you to do so. Here are the steps.
This will show all the negative numbers as positive. But, in actual these all are still negative number, just formatting is changed.
If you select a cell and look at the formula bar, you can check, it’s still a negative number. So, when you use it in further calculation it will act as a negative number.
If you are a VBA lover then you can use a simple code to convert all the positive numbers into negative one.
Dim myCell As Range
For Each myCell In Selection
If myCell.Value <> "" Then
If IsNumeric(myCell.Value) Then
myCell.Value = Abs(myCell.Value)
To use this code, you just need to select the range of negative number and run this macro. First it will check each cell of selection if there is a numeric value in it or not and then convert it to a positive value.
Quick Alert: Take care of one thing that once you run this code you can’t undo your action.
A dynamic method.
Yes, you can use power query to convert a negative number into a positive number. And, the best part is it’s a one-time setup. Just follow these simple steps.
Now, you have a new worksheet in your file with positive numbers and whenever you update (add or delete) your original values, you just need to refresh the query.
This is a dynamic method and one time set-up.
As I said, to turn a negative number into a positive number you don't need to use rocket science. Even one method can be sufficient, but I have listed all these methods to help you to handle different situations.
I am sure you found all these methods helpful. And, please don’t forget to share it with your friends, I am sure they will appreciate it.
Now, you have to do one thing for me. I have a strong gut feeling that there should be one or two more methods which you can use to turn a negative number into positive. And if you know any of them, please share with me in the comment section. I would love to hear from you.