Most of the people who work with Excel spreadsheets deal with numbers in large/small datasets. And when you work with numbers, you will have all types of it (positive, negative, decimals, date/time). One of the common tasks a lot of us need to do often is to convert these numbers from one format to another. And probably the most common one is when you have to change negative numbers to positive numbers (remove the negative sign) for some calculations. And again, there are multiple ways you can do this in Excel. In this tutorial, I will show you some simple ways to change negative numbers to positive in Excel (using formulas, a copy-paste technique, and other awesome methods). So if you’re interested, keep reading!
Multiply with minus 1 to Convert Negative Number to Positive
If you have a column full of numbers and you want to quickly get the numbers where negatives have been converted into positive, you can easily do that by multiplying these negative values by -1. But you also have to ensure that you’re only multiplying the negative numbers and not the positive ones. Suppose you have a dataset as shown below:
Below is the formula that will convert negative numbers to positives and keep the rest unchanged:
The above formula uses the IF function to first check whether the number is positive or not. If it’s positive, the sign is not changed and if it’s negative, a negative sign is added to the reference, which ends up giving us a positive number only. In case you have text values in the dataset as well, this function is going to ignore that (and only negative values will be changed) Now that you have the required result, you can convert these formulas to values (and copy it over the original data in case you don’t need it)
Use the ABS function to Change all Negative Numbers to Positive
Excel has a dedicated function that strips out the negative sign and gives you the absolute value. .. the ABS function Suppose you have the dataset as shown below and you want to change negative values to positive values. Below is the formula that will do this:
The above ABS function doesn’t impact the positive numbers but converts negative numbers into positive values. Now that you have the required result, you can convert these formulas to values (and copy it over the original data in case you don’t need it)
Multiply Using Paste Special To Reverse the Sign
In case you want to reverse the sign of the number (i.e., change negative to positive and positive to negative), you can also use this paste special multiplication technique. Suppose you have the dataset as shown below and you want to reverse the sign: Below are the steps to reverse the sign using Paste Special: You would notice that the above steps instantly change reverses the sign of the number (i.e., positive numbers become negative and negative numbers become positive). But what if you only want to convert negative numbers to positive numbers and not the other way round? In that case, you somehow first need to select all the negative numbers and then follow the above steps. Here is how to select only the negative numbers in Excel: The above steps would select only those cells that have a negative sign. Now that you have these cells selected, you can use the Paste Special technique to change the sign of only the negative numbers. This technique has two advantages over the formula technique (the two methods covered before this):
Flash Fill To Remove the Negative Sign
Flash Fill is a new functionality that was introduced in Excel 2013. It allows you to quickly identify patterns and then give you the result where the pattern has been applied to the entire dataset. One use of this is when you have names and you want to separate first name and last name. As soon as you type the first name in an adjacent cell a couple of times, Flash Fill will identify the pattern and give you all the first names. Similarly, you can use it to quickly remove the negative sign from a number, while the positive values remain unchanged. Below is a dataset where I have the negative numbers and I want to change these to positives values. Below are the steps to change negative numbers to positive numbers using Flash Fill:
The above steps would give you the expected result, where the negative sign has been removed. One thing you need to remember when using this method is that Excel relies on guessing the pattern. So you will have to at least show Excel that you are converting a negative number to positive. This means that you will have to manually enter the expected result until you have covered at least one negative number.
Convert Negative Numbers to Postive with a Single Click (VBA)
And lastly, you can also use VBA to convert negative values to positive values. I would recommend using this method if doing this is something you have to do often. Maybe you regularly get the dataset from a database or a colleague and you have to do this every time. In such a case, you can create and save the VBA macro code to the Personal Macro Workbook and place the VBA in the Quick Access Toolbar. This way, the next time you get a dataset where you have to do this, you simply select the data and click on the icon in the QAT… … and you’ll be done! Don’t worry, I will show you the exact steps to get this up and running. Below is the VBA code that will convert negative values to positive values in the selected range: The above code uses the For Next loop to go through each cell in the selection. It uses the IF statement to check whether the cell value is negative or not. If the value is negative, the sign is reversed, and if it’s not, it’s ignored. You can add this code to a regular module in a workbook (if you only want to use this in that workbook). And in case you want to use this macro code in any workbook on your system, you can save it in a personal macro workbook. Here are the steps to get the Personal Macro Workbook (PMW). Here are the steps to save this code in the PMW. Now let me show you how to add this code to the Quick Access Toolbar (steps are the same whether you save this code in a single workbook or in the PMW) Now you will have the macro icon in the QAT.
To use this macro with a single click, simply make the selection and click on the macro icon. I hope you found this Excel tutorial useful. You may also like the following Excel tutorials:
Convert Date to Text in Excel Show Negative Numbers in Parentheses (Brackets) in Excel How to Add Plus Sign Before Numbers in Excel Convert Text to Numbers in Excel How to add serial numbers to rows in Excel How to Stop Excel from Changing Numbers to Dates
