You can convert a negative number into a positive one in Excel with or without using any function. The fact is—It’s not rocket science.
The only challenge for you is “Which cells contain the negative numbers?” Once you determine the cell, you can multiply that number with -1. However, we are assuming that your dataset is too complex.
So, here are the ways to convert negative number to positive in Excel:
1. Use The ABSOLUTE Function
The acronym of absolute values is ABS, meaning the ABS function discards the sign of the numbers and returns a magnitude only. We use the ABS function when we work with numerical data independent of its positive or negative features.
The syntax of the ABS function is:
As you can see the Absolute function contains only one argument. We can replace this argument with the range of cells as well. For example: =ABS(A9:A14). The function will return a result sequentially.
IMPORTANT: The ABS Function even works when you have mixed sign numbers (positive and negative) in your datasheet. Just supply a number and it will return the absolute result.
So, this is how you remove a negative sign in Excel using a function. Now, let’s see how to change a negative number to a positive while entering the formula without using any function.
2. Apply A VBA Code To Convert All Negative Numbers To Positive
A workable VBA code for Excel is a shortcut to your time management skills. This method even works for decimal numbers. If you are an intermediate skill holder in Excel, you can read and modify the codes. And if you are a beginner, just copy the code and paste it as follows:
- Go to the Developer Tab and Select the Visual Basic Option.
- Click on the Insert Button and select the Module Option.
- Paste the code that we’ve provided after these steps.
- Click on F5 or press the RUN Button from the top-left side of the menu bar.
- Return to the spreadsheet to see the changes.
The code for step 3:
Sub ConvertNegativeToPositive()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
' Set the worksheet where your dataset is located
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
' Set the range of cells where you want to convert negative numbers to positive
Set rng = ws.Range("A1:D10") ' Replace "A1:D10" with your actual range address
' Loop through each cell in the specified range
For Each cell In rng
' Check if the cell contains a negative number
If cell.Value < 0 Then
' Convert the negative number to positive
cell.Value = Abs(cell.Value)
End If
Next cell
End Sub
Note: This code changes all the negative numbers into positive inside the whole worksheet. So, run the code only when you want to remove all the negative signs in Excel. The code doesn’t work for a specific cell.
Area of Modification: You have to modify two parameters while running this code.
- Replace the Sheet name: By default the Worksheet name is set to Sheet1. If you have changed the worksheet name then provide that name inside the quotation mark from the line ThisWorkbook.Sheets(“Sheet1“).
- Change the Actual Range Address: Since our dataset wasn’t long enough, we selected the range of cells from A1:D10. Change the range of cells from the line Set rng = ws.Range(“A1:D10“).
3. Use The Power Query Editor
How to change negative to positive in Excel without formula? The answer is:- Using the Power Query Editor and the Paste Special Option.
If you don’t know what a power query is. In short, Power Query is a data transformation and data preparation tool available in Microsoft Excel. The Power Query Editor is available on both Mac and Windows. Let’s first look at the steps for MacOS.
Utilize the Power Query Editor For Mac:
Go to the Data Tab and Select the Option Get Data (Power Query). Select the Blank Table option. Enter the Data Manually or Copy paste data into the cells. Click on Next and Right-click on the column. Select the Transform Column option and click on Absolute Value. Click on the Close and Load option from the Home Tab.
Notice all the negative data converted to its positive form. You can perform this step in Windows also.
For Windows Users:
For Windows Users, go to Data Tab>From Table>Insert Table Automatically By Selecting Range>Select My Table Has Headers>OK.
Right-Click on the column that has negative values. Select The Transform Option and click on Absolute Value.
4. Use the Paste Special Option to Multiply By -1
Drawback: This method doesn’t work with a dataset containing mixed numbers (both positive and negative). The positive numbers will convert to negative if multiplied by -1 using this method.
The Paste Special option allows us to control and customize how copied data is pasted in a new location. The paste special dialog box has a variety of options to deliver. Such as- Formulas, Values, Formats, Comments, validation, All using source theme, All except borders, column widths, conditional formats, and different mathematical operations (multiply, divide, add, and subtract). Another noteworthy attribute of this option is whether you want to skip blank cells or transpose.
Let’s say we have a dataset containing negative numbers from A7:A10. We will type -1 in any blank cells and copy it using Ctrl+C. Then we will select the range of cells from A7:A10 and use the Right-click button to select the Paste Special Option. Click on the arrow icon to open the Paste Special Dialog Box. Select the Multiply Option and click OK.
Result: The negative signs will get removed and the number will become positive.
5. Use Custom Number Format
Disclaimer: This method only changes the format of the numbers. In reality, the negative numbers will still be negative. You can cross-check from the Formula bar.
If you look at the formula bar, the value is still negative. So, when should you use this method with such a huge disadvantage?
Although this technique is a visual formatting technique, the underlying values won’t change. So, whenever you want to represent a dataset without altering the actual data, you can use this method.
But do remember one thing. If you want to perform calculations based on the formatted data, this method is not for you. Because during calculations, Excel will use the original values, not the formatted ones.
Select the numbers you want to convert to positive values and press Ctrl+1 on your Keyboard to open the format cells dialog box. Then click on the custom options and inside the dialog box type #,###;#,###. Notice that the sample number changed from negative to a positive value.
6. Perform a Flash Fill
The flash fill feature works as a copycat. It works similarly like the Paste Special option. However, the process is slightly different.
Note: The first number you are entering manually removing the minus sign should represent a negative value.
Flash fill requires an example. So you have to type the absolute value of the number in a separate column parallelly to utilize the flash fill feature.
Suppose, we have a mixture of numbers (-2, 0.02, -15.9, 9) from cells B1:B4. Notice that the first number is negative here. In its adjacent cell C1, we type in 2 (the absolute value of the negative number) and press Enter. Then press Ctrl+E to use the Flash Fill feature.
Flash Fill Shortcut Key Not working?
Go to the Data Tab and select the Flash Fill feature. Your shortcut key isn’t working because you already have an assigned operation for that same shortcut key.
Frequently Asked Question
Question: Can you make all negative numbers positive in Excel?
Answer: It is possible to make all negative numbers positive in Excel using the ABS function, a custom VBA code, Paste Special Option, or even the Power Query Editor. In short, all negative signs can be removed with or without using any function.
Conclusion
In our opinion, if you are obsessed with formulas then use the ABS function to change negative values to positive. The ABS Function is easy to use and doesn’t require any pro skills.
