Excel lets you convert a negative value into a positive number without much effort. Within a cell, you can simply turn values into whatever you want and it really does not need you to use formulas or other complex procedures.
How to make negative numbers positive in Excel is the real query we are going to discuss in this post. You would have noticed that it becomes problematic with a large dataset while trying to convert a negative number into a positive one. Not always, you will change the values effortlessly in fact it is a waste of time as well. Also, there is not a single built-in function that can help you in performing this task.
Let’s get into see how you can do it:
Hack 1: Use the ABS Function to Make Negative Numbers Positive in Excel
With the ABS function, Excel lets you expose the negative sign while providing the absolute value. let’s assume that your dataset has negative values that you need to convert into positive numbers. For this, the given formula can be used:
=ABS(A2)
Remember that the ABS function directly does not influence the positive numbers however it helps in converting negative values into positive numbers. The required outcome is in front of you now and you can copy it to paste wherever you need.
Similar to other functions, the ABS function also has limitations in that it only deals with numbers. If you apply the ABS function to the cells in which text data type is added, you will instantly get #VALUE! error.
Hack 2: Use Paste Special to Convert Negative Numbers to Positive ones in Excel
With Paste Special, you can easily convert negative values into positive ones. For this method, you need to have a list in your dataset that only contains positive values. In case, you don’t have a list with positive values only, you need to convert the list to absolute values with the help of above mentioned ABS process.
And then follow the steps given below:
- Add -1 in an empty cell. The cell must be away from the list.
- Now, copy the cell by right-clicking on it.
- Choose the complete list of values. In case of a long list, you need to choose the first cell on the list and press the SHIFT + CTRL + Down Arrow key from the keyboard.
- Press SHIFT + Command + Down Arrow key for Mac users.
- It will highlight the cells till the last cell.
- Right-click on the selected cells and choose the Paste Special option given in the Context menu.
- Now, click on the Paste Special option and the Paste Special dialog box will pop up.
- Choose the Multiply option given under the Operation menu from the dialog box.
- Press OK.
- This step will multiply the selected cells with the value you copied in the second step.
You will simply notice the values are being converted into positive.
Hack 3: Use VBA Code to Convert Negative Values into Positive
For VBA lovers, coding helps them perform any function without using other tricks. With the code given below, you can easily convert the negative sign into a positive one instantly.
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
- To apply this code, you simply have to select the cells in which you need to change the values.
- Once the cells are selected, run this macro code.
- The code will loop through each cell and if a numeric value is found, it will convert it into a positive value.
Once the code started working, you cannot stop it until it ends its function.
Let’s Wrap Up:
In this post, you have learned how to make negative numbers positive in Excel with the help of three different hacks. Each method is significant enough to tackle your needs that’s why you can try any of the above methods without risk. Excel never holds you back from trying new functions. Keep trying and exploring Excel.
