I will show you a few ways to change negative numbers to positive numbers and back again in Excel.
Don't forget to download the accompanying workbook so you can follow along with the tutorial.
Method 1 - Convert Negative Values to Positive Values
You can use the "paste special" method to change numbers from negative to positive or positive to negative.
Add a cell with -1 in it near your negative numbers:
Copy the cell with the -1, just select the cell and hit Ctrl + C:
Select the negative numbers:
Right-click over the negative numbers and click Paste Special:
Select the option Multiply and then hit OK:
Now, you have a colum of positive values:
Simply delete the -1 and you are done.
Method 2 - Convert Positive Values to Negative Values
To convert any series of positive numbers to negative numbers, simply follow the exact same steps as in Method 1 above; the only difference is that you will start with positive numbers in the original list this time. Remember to keep -1 the same since multiplying a positive number by a negative number will create a negative result.
Method 3 - ABS() Function to Convert Negative Value to Positive Values
You can also use the ABS() or absolute value function to return a number without its sign. This will create a series of positive numbers from any set of negative numbers or a mix of negative and positive numbers.
Whereas Method 1 may cause you to end up with mixed results if you had some negative and some positive values in the starting list, this method will only allow for positive numbers to be created, no matter what.
Enter =ABS(Cell Reference) into the first cell next to the list of numbers: Note that the cell reference refers to the first number in the list.
Copy the function down the entier list by selecting the first cell, B1, and double-clicking the bottom-right side of the cell:
Now, you can stop here if you don't mind having two columns of data, one containing the raw data and the other containg functions, or, you can continue to make it one list - copy the new column of numbers (the positive ones):
Then select the original column of numbers and hit Alt + E + S + V and Enter (this will copy-paste-special values over the original list):
Now, delete the second column that has all of the functions and you are done.
Notes
Use whichever method is easiest for you. I prefer Method 3 just because I can do it in a couple seconds using keyboard shortcuts that I am familiar with.
The benefit of the first method is that it can be used to go from positive to negative or negative to positive.
The benefit of the second method is that, if you want a list of only positive values and the original list contains a mix of positive and negative values, the second method will ensure that all values end up being positive, whereas Method 1 and 2 will simply reverse the sign of each number, regardless of whether or not all of the values in the list are positive, negative, or a mix of the two.