How to Convert Negative Number into Positive in Excel

Last week, I got an email 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.

There is no #rocketscience in this.

But have you ever checked how my different methods you have to do this? Well, I am always curious to know about different methods to do a task in Excel.

So, this time I grabbed a paper and listed all the methods which I can use to convert a negative number into positive.

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’d like to share all those methods with you.

#1. Multiply with Minus One to Convert a Positive Number

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.

=negative_value*-1

Below you have a range of cells with negative numbers. So, to convert them into positive you just need to enter the formula in cell B2 and drag it up to the last cell.

negative number into positive with simple multiply with us one

Note: If you have mixed numbers (both positive and negative) then you can use below method, instead if below one.

=IF(A1<0,A1*-1,A1)

#2. Convert to an Absolute Number with ABS Function

Syntax:

ABS(number)

You just have to refer a negative number into the function and it will turn it into a positive value.

  1. In the below example, you have negative values from range A2:A11.
  2. Enter =ABS(A2) into B2 and drag it up to the last cell.
negative number into positive with abs function

Note: This function works even when you have mixed numbers (both positive and negative).

negative number into positive with paste special
  1. First of all, in any cell in your worksheet, enter -1.
  2. After that, copy it.
  3. Now, select the range of cells in which you have negative numbers.
  4. Right Click -> Paste Special -> Operations -> Multiply.
  5. In the end, click OK.

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 this method is quick and easy to use, and you don’t need any formula.

#4. Remove Negative Sign with Flash Fill

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.

negative number into positive with flash fill
  1. First of all, in cell B2, enter the positive number for the negative number you have in cell A2.
  2. After that, come to cell B3 and press shortcut key Ctrl + E.
  3. At this point, in B column, you have all the numbers in the positive form.
  4. Now, click on the small icon you have on the right side of the column B and select “Accept Suggestions”.

Congratulations, you have converted all the negative numbers into positive with flash fill.

Note: This method is also not a dynamic one, but quick and easy to use.

#5. Apply Custom Formatting to Show as 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.

Here are the steps to this.

  • First of all, select the range of the cells you need to convert into positive numbers. 
  • After that, press shortcut key Ctrl + 1. It will open the custom formatting options.
  • Now, go to “Custom” and in type input bar, enter “#,###;#,###”.
add custom format in input bar to convert negative number into positive
  • In the end, click OK.

This will show all the negative numbers as positive. But, in actual these all are still negative number, just formatting is changed.

negative number into positive still negative in formula bar

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.

#6. Run a VBA Code to Convert to Positive Numbers

Sub numberP2N()
Dim myCell As Range
For Each myCell In Selection
If myCell.Value <> "" Then
If IsNumeric(myCell.Value) Then
myCell.Value = Abs(myCell.Value)
End If
End If
Next myCell
End Sub

Note: Once you run this code you can’t undo your action.

#7. Use Power Query to Convert Get Positive Numbers

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.

  • First of all, select any of the cells from the data range where you have negative numbers.
convert negative number into positive using power query select cell
  • After that, go to Data tab -> From Table.
convert negative number into positive using power query click on from table
  • It will convert the range into a table and load it in power query editor.
convert negative number into positive using power query data in editor
  • Now, right click on the column, go to Transform -> Absolute Value.
convert negative number into positive using power query click absolute value
  • In the end, in power query editor, go to Home Tab -> Close -> Close and load.
convert negative number into positive using power query click on load

Conclusion

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.

Now, you have to do one thing for me.

Do you know any other method for this?

Please share your views with me in the comment section. I'd love to hear from you. And, please don’t forget to share it with your friends, I am sure they will appreciate it.

  • Harsha Vardhan

    Thanks Puneet !!

    • Puneet Gogia

      I’m so glad you found it useful.

  • Meni Porat

    Thank you, Puneet.
    Great methods.
    However, some of the techniques might not work as expected when you have mixed negative and positive numbers.

    • Puneet Gogia

      Yes, #1 and #3 will not work.

  • Mustafe Osman

    Thanks Puneet.

    How i can convert the only negative numbers. And posative must be zero Like this example.
    A. B.
    -2. 2
    33. 0

    • Puneet Gogia

      Use VBA or an conditional formula.