Change Negative Number into Positive in Excel to streamline your data processing and ensure accurate calculations, especially when dealing with financial, statistical, or scientific data. This transformation is crucial in scenarios where absolute values are needed or when you want to normalize data for comparison. Whether you’re rectifying data entry errors, adjusting financial statements, or preparing data for analysis, this guide will provide you with clear, concise instructions to effectively convert negative numbers into positive ones in Microsoft Excel. By mastering this technique, you’ll enhance the consistency and reliability of your datasets, leading to more precise and meaningful insights.
In this tutorial, I’ll demonstrate a few quick ways to convert negative Excel values to positive ones (using formulas, a copy-paste technique, and other awesome methods).
This Tutorial Covers:
- Change Negative Number into Positive number multiplying by minus 1
- To convert negative numbers to positive ones, use the ABS function
- Use Paste Special to Multiply and Reverse the Sign
- To Remove the Negative Sign, Flash Fill to change negative number into positive number
- With just one click, change negative numbers to positive ones (VBA)
1. Change negative number into positive number multiplying by minus 1
By multiplying these negative values by -1, you can rapidly determine the numbers where negatives have been changed into positives if you have a column of numbers and desire to do so.
However, you must also be careful to only multiply negative values and not positive ones.
Assume you have the dataset as follows:
The steps to remove negative sign in excel are described below:
Step 1: Enter the following formula in cell B2 to turn negative numbers into positives while maintaining the rest of the value unchanged:
=IF(A2>0,A2,-A2)
Step 2: After that, fill the rest of the cells using the autofill handler. The result looks like below:
The IF function is used in the formula above to determine whether the integer is positive or not first. If the reference is positive, the sign is left alone; if it is negative, a negative sign is added, leaving us with only a positive number.
If the dataset also contains text values, this method will disregard those (and only negative values will be changed)
Once you’ve obtained the desired outcome, you can translate these formulas into values (and copy the original data over in case you don’t require it).
2. To convert negative numbers to positive in Excel, use the ABS function
Excel contains a special function called the ABS function that removes the minus sign and returns the absolute number.
Let’s say you need to convert negative values to positive values in the dataset depicted below Excel sheet.
How to remove negative sign in excel using ABS Function is shown below:
Step 1: Enter the following formula in cell B2 to turn negative numbers to positive numbers while maintaining the rest of the value unchanged:
=ABS(A2)
Step 2: After that, fill the rest of the cells using the autofill handler. The result looks like below:
Positive numbers are unaffected by the aforementioned ABS algorithm, however, negative numbers are transformed into positive values.
Once you’ve obtained the desired outcome, you can translate these formulas into values (and copy the original data over in case you don’t require it).
Note: The ABS function has the small drawback of only functioning with numbers. If part of the cells contains text data and you attempt to utilize the ABS function, you will receive a #VALUE! error.
3. Use Paste Special to Reverse the Sign (Multiply the number)
Use this paste-specific multiplication method if you wish to shift the sign of the number from negative to positive or from positive to negative.
Let’s say you have the dataset depicted below and wish to change the sign.
The steps to remove negative sign in excel using Paste Special are described below:
Step 1: Enter -1 into any blank cell on the spreadsheet.
Step 2: Copy this cell (which has the value -1)
Step 3: Decide the range you wish to reverse the sign for. Select any cell by doing a right-click on it. Toggle to “Paste Special”. The “Paste Special” dialog box will then be displayed.
Step 4: Choose “Values” from the “Paste” menu. Choose “Multiply” from the “Operation” options. Hit “OK.”
You would observe that the aforementioned actions immediately reverse the number’s sign (i.e., positive numbers become negative and negative numbers become positive).
What if, however, you simply want to change negative integers into positive ones and not the other way around?
In that scenario, you must first choose all of the negative numbers before continuing with the following procedures. Before that, you will need to copy cell which has the value -1.
Here’s how to limit your Excel selection to negative numbers:
Step 1: Choose the whole set of data.
Step 2: Press the F key while keeping the Control key depressed. The “Find and Replace” dialog box will then be displayed. Enter – in the “Find What” field (the minus sign). Select “Find All”. While maintaining press, “CTRL+A” key.
The aforementioned procedures would only choose cells with a negative sign.
Step 3: Now that you have these cells selected, use the right-click menu to choose any cell. Set “Paste Special” to “On.” After that, the “Paste Special” dialog box will appear.
Step 4: Choose “Values” from the “Paste” menu. Choose “Multiply” from the “Operation” options. Hit “OK.”
You would observe that the aforementioned actions immediately reverse the number’s sign (i.e., negative numbers become positive). You can now delete the cell which has the value -1.
Compared to the formula technique (the two ways discussed before this), this methodology offers the following two advantages:
- You don’t have to add a second column and use a formula to populate it with the result. This can be applied to an existing dataset.
- The formulas do not need to be changed into values (as the result you get is already value and not a formula)
4.To Remove the Negative Sign, Flash Fill to change negative number into positive number
Flash Fill is a brand-new feature that debuted in Excel 2013.
It enables you to quickly spot patterns and then provides the outcome after applying the pattern to the complete dataset.
When you have names and wish to distinguish between first name and last name, you can use this technique. Flash Fill will recognize the pattern and offer you all the first names as soon as you type the first name in an adjacent cell a few times.
The positive values of a number can be left untouched while the negative sign is swiftly removed using a similar technique.
I want to convert the negative numbers in the dataset below from negative to positive values.
Flash Fill is a brand-new feature that debuted in Excel 2013.
It enables you to quickly spot patterns and then provides the outcome after applying the pattern to the complete dataset.
When you have names and wish to distinguish between first name and last name, you can use this technique. Flash Fill will recognize the pattern and offer you all the first names as soon as you type the first name in an adjacent cell a few times.
The positive values of a number can be left untouched while the negative sign is swiftly removed using a similar technique.
I want to convert the negative numbers in the dataset below from negative to positive values.
The steps to use Flash Fill to convert negative numbers to positive ones are as follows:
Step 1: Enter the anticipated outcome manually in the box next to the one with the data. I’ll manually input 156 in this instance. Enter the anticipated outcome in the cell beneath it (783 in this example).
Step 2: As a consequence, choose the cells where you want to enter the positive values.
Step 3: Choose the “Data” tab. Then choose “Flash Fill” from the list of options under “Data Tools.”
You would get the desired outcome by following the aforementioned procedures, where the minus sign has been eliminated.
When employing this technique, keep in mind that Excel relies on pattern guessing. Therefore, you must at least inform Excel that you are changing a negative value to a positive one.
This implies that until you have covered at least one negative number, you will need to manually enter the intended outcome.
5. With just one click, change negative numbers to positive ones (VBA)
There is a quick option to change negative to positive with VBA.
If you need to do this frequently, I advise adopting this method. Maybe you must do this each time since you frequently obtain the dataset from a coworker or a database.
You may then construct a VBA macro, save it to the Personal Macro Workbook, and add it to the Quick Access Toolbar. As a result, the next time you receive a dataset where you must do this, all you have to do is select the data and click the icon in the QAT to successfully complete!
The VBA code that will change negative values in the chosen range to positive values is listed below:
Sub ChangeNegativetoPOsitive()
For Each Cell In Selection
If Cell.Value < 0 Then
Cell.Value = -Cell.Value
End If
Next Cell
End Sub
The For Next loop is used in the code above to cycle through each cell in the selection. The IF statement is used to determine whether or not the cell value is negative. The sign is changed if the value is negative; otherwise, it is ignored.
This code can be added to a workbook’s normal module (if you only want to use this in that workbook). Additionally, you can save this macro code in a personal macro workbook for use in any workbook on your system.
Let me now demonstrate how to include this code in the Quick Access Toolbar (steps are the same whether you save this code in a single workbook or in the PMW).
Step 1: The workbook containing the data should be opened. To access the “Microsoft Visual Basic for Applications” box, use “ALT+F11.” After then, double-click on the sheet containing the data with your mouse’s left button. The VBA code is then typed or pasted.
Step 2: In the QAT, select the “Customize Quick Access Toolbar” option.
Step 3: Select “More Commands.”
Step 4: “Choose commands from” by selecting it from the drop-down menu in the Excel Options dialog box. Select Macros. This will display all of the workbook’s macros (or the Personal Macro Workbook).
Step 5: Click on the ‘Add’ button. Click OK.
The macro icon is now present in the QAT.
Make your choice, then click the macro button to instantly activate this macro.
Note: You must save the worksheet in the macro-enabled format if you’re saving the VBA macro code in it (XLSM).
I hope you learned something from this Excel tutorial.
For ready-to-use Dashboard Templates:
