Make Negative Numbers Positive in Excel and Vice Versa

Add to Favorites
Author: | Edits: don

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.

  1. Add a cell with -1 in it near your negative numbers:
    4c62e8b2a7b933bb8cb9974356988f2b.png
  2. Copy the cell with the -1, just select the cell and hit  Ctrl + C:
    7c5582eeb4c4c59cbec61626b110754c.png
  3. Select the negative numbers:
    6fe4b8f8a2dd58982b1b03707615a5b8.png
  4. Right-click over the negative numbers and click Paste Special:
    fbab079da5401b91107ddf18dcb9cc44.png
  5. Select the option Multiply and then hit OK:
    21b40f9c546d9e457e2ae8681dbf164c.png
  6. Now, you have a colum of positive values:
    9b9cab49ac69d3a0c871a44a2038a8cf.png
  7. Simply delete the -1 and you are done.
    55754eeec4d450a1c87466dbb5cdb8fe.png

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.

  1. Enter =ABS(Cell Reference) into the first cell next to the list of numbers:
    61d86bfbb0d22230cf31d2765c30df0b.png
    Note that the cell reference refers to the first number in the list.
  2. Copy the function down the entier list by selecting the first cell, B1, and double-clicking the bottom-right side of the cell:
    61b17157522e27304017941257401213.png
  3. 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):
    78a28001bf74206f856e957e81b0578f.png
  4. 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):
    bb67b684cd4c368b52e37d40e8e94e30.png
  5. Now, delete the second column that has all of the functions and you are done.
    6f31d36876bb6fde9895af76bdfc64ea.png

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.


Excel Function: ABS()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Make All Numbers in a List Positive in Excel
Tutorial: Take a list of numbers and make them all positive, regardless of whether the list contain...
Convert Time to Minutes and Vice Versa in Excel
Tutorial: How to convert a time into minutes in Excel and also how to get minutes back into a time f...
Make Column Headings Numbers instead of Letters - Make R1C1 Style References in Excel 2007
Tutorial: In Microsoft Excel you reference columns as letters by default - A1, B3, C5, etc. But you ...
Format Cells in The Number (Numerical) Number Format in Excel
Macro: This free Excel macro formats selected cells in the Number or Numerical number format in E...
Make Threshold Alert Popups in Excel - Easy Version
Tutorial: How to make a popup window in Excel that appears when a value reaches a certain level. Yo...
Format Cells in The Long Date Number Format in Excel
Macro: This free Excel macro formats a selection of cells in the Long Date number format in Excel...
Tutorial Details
Excel Function: ABS()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course