We use Excel to work with a lot of numbers. So often we may need to change some negative numbers to positive or remove negative signs from numbers in a workbook. You may want to change negatives into positives only, or you may want to alter the positive and negative states of numbers.
In this guide, we will share with you 6 ways that you can remove the negative signs or minus signs from a number or a range of numbers. We will share some basic methods to remove the negative signs along with some advanced methods to help you in your different use cases.
So let’s dive in.
Contents
6 Functional Ways To Remove Negative Signs In Excel
There are several ways to remove the leading minus signs in Excel which is otherwise known as the negative sign. We can use different functions to remove the minus sign by changing negative numbers into positive ones. Alternatively, we can also change positive numbers into negatives.
Or we can just remove negative signs from a range of numbers without touching the positive numbers. We can even use advanced methods like VBA to remove negative numbers from a worksheet with a single click.
We will show you 6 working methods to remove negative signs in this guide. We will also use examples with pictures so that you can follow along. Throughout this guide, we will use this simple sample datasheet so that you can quickly understand the process.
Let’s start.
Using the ABS Function: To Change All Negatives Into Positives
The ABS function is Excel’s dedicated function to swiftly remove negative signs. The ABS function takes numbers and returns their absolute values which are always positive. So using the ABS function we can quickly change the negative values or remove the negative signs from a cell or a range of cells.
Let’s see the process of using the ABS function:
1. First, select a cell in any other row than the row containing your dataset. In our case, we chose the B2 cell as shown in the picture.
2. Then in the formula bar, we wrote the ABS formula as shown in the picture (1):
=ABS(A2)
This will remove the negative sign of the A2 cell and put it in the B2 cell. This is seen in the picture below (2).
3. But what if we wanted to remove all the negatives in this range? We can do this by first getting the absolute value of the first cell in the selected dataset. For example, we will be choosing the B1 cell. And we will similarly enter the formula:
=ABS(A1)
in the cell as shown in the picture (3)
4. Then, we will press Enter to get the absolute value of cell A1.
5. Now, we can use the Fill handle to copy the formula over the other cells. The fill handle is a great way to swiftly copy formulas to other cells in a worksheet. For example, in this case, we can get the fill handle by hovering our mouse on the bottom right corner of cell B1 as shown in the picture (4).
After we get the Fill handle, we can simply drag the plus sign below to copy our ABS formula in the consecutive cells below. Then we will get the positive values of Cell A1:A8 as seen in the picture (5).
As you can see the output has removed the negative signs from the range of numbers. Using the ABS function has its perks as it will quickly remove the negative signs from the worksheet. But If you have texts in the same column, then the ABS function will not work and return an error as seen in the picture. (6)
But, fear not. We also have a solution to keep the text while removing the negative signs. To do that you can check out the 2nd method in this guide.
Using the IF Function
The IF function simply looks at logical comparisons in Excel. We can use the IF function to multiply the negative values in the cells in our worksheet with (-1) to get a positive value and remove the negative sign.
This function can come in handy when you are working with both texts and numbers in a worksheet and you want to only delete the negative signs from the worksheet. This will not change your positive numbers to negatives. But if you are looking for a solution for that just skip over to method number 3 in this guide.
So, to only remove the negative signs from the worksheet while ignoring the positive values we can follow these steps:
1. Similar to the first method, we will choose a cell next to the original cell containing our sample data. In our case, it is the B1 cell.
2. In the formula bar, we will type in this formula as seen in the picture (1):
=IF(A2>0,A2,-A2)
This formula checks if the value of the cell is bigger than 0 and then multiplies the value in the cell by either a positive value or a negative.
So according to our formula, if the value in cell A2 is greater than 0, Excel will keep it as is. But if the value is negative, then it will be multiplied by a negative. This will turn the negative value into a positive value.
3. Similarly, If we want to apply this to the whole range of cells then we can just write the formula for the A1 cell.
4. And then we can use the Fill handle to copy the formula to the consecutive cells below as seen in the picture (3).
As we can see in the output, all the negative values in the cells have changed to positive ones. But this formula will just ignore any texts that you might have in your range of data and will not return an error. But this formula will multiply the numbers in the cell with a (-1) if it passes a condition.
So, If you want to change the negatives while changing the positives we can use this function. But if you want to alter the positive numbers to negatives and negative numbers to positives then you can use the 3rd method below.
The Paste Special Method
The Paste special method alters the current state of the positive and negative values of a cell or a range of cells. So if you want to change positive values to negative values and negative values to positive ones, you can use this method.
The process for this method is quite simple as we will discuss here:
1. For this method, you can simply click on any cell other than the data range. In our case, we have chosen B1. Then, double-click the cell or in the formula bar enter -1
2. After entering -1 like the picture, we will just copy the cell by simply clicking CTRL +C (for windows) or Command+C (for mac).
3. Now, just select the cell range containing your numbers and right-click on the selected range.
4. From there, on the right-click menu simply click on Paste special as we have shown in the picture (2).
5. As you can see, the paste special option is right there under the paste icon in the menu. Now after clicking it you will be presented with a pop-up. Now make sure you check ‘Values’ on the left-hand side and ‘Multiply’ on the right-hand side just like the image here (3).
6. Now you will see that your positive numbers have changed into negatives and your negative numbers have changed into positives. Quite simple, right?
Now you can just delete the -1 from the cell that you put in before. And you are done. As you can see, with this process you will not need a separate column and you don’t have to write any formulas.
Using Find and Replace
We can also use the Find and Replace feature in Excel to simply replace the negative signs with nothing. This is one of the quickest ways to remove only the negative signs and you can do it with only a few clicks.
Let’s see the process.
1. First, select the number range that you want to remove the leading minus signs.
2. Then, Click on the Find and Select menu from the Home tab and choose Replace as seen in the picture (2). For a nifty keyboard shortcut, you can also press CTRL+H to bring up the Find and Replace menu directly.
3. Enter a negative sign (-) in the ‘Find what’ space and just keep the “Replace with” field blank. Click on Replace all and you are done.
4. As you can see, you will see a dialogue box that will show you how many negative signs you have removed. In our case, it was 4.
Using VBA
All of the methods that we have shown you require some steps to complete. But if you are regularly working with datasheets where you have to remove negative signs you can create a VBA (Visual Basic for Applications) to create a macro. You can use this macro to remove all negative signs from a selected range with a single click.
Don’t worry, You don’t need any programming knowledge to do this. Just follow these steps and copy and paste the code with the process.
Now let’s look at the steps:
1. Select the Developer tab and click on Visual Basic on the far left-hand side.
2. Now a new window will pop up. Simply copy and paste the piece of code below into the empty box on the right side. Don’t worry. We will explain the function of the code to you.
Sub ChangeNegativetoPositive()
For Each Cell In Selection
If Cell.Value < 0 Then
Cell.Value = -Cell.Value
End If
Next Cell
End Sub
3. After you have put the code in the box as we have shown in the picture, press CTRL+S to save the code and exit the window by clicking on the red cross button in the right-hand corner.
4. Now, to use this macro in one click you would want to bring the function to the quick access toolbar. Don’t worry, we will show you exactly how to do that. Just follow along. First, click on the quick access toolbar button and press more commands.
5. Now, a new pop-up will appear. Just put macros from the menu and select the file with the name ChangeNegativetoPositive and click add.
6. After that you just press ok and the menu will close and a new button will be added to your Quick Access Toolbar (QAT) as you can see in the picture below. Now you need to just simply choose the cells or the range of cells you want to remove the negative signs from and click on the newly added button.
And now you are done. As you can see in the output, any negative signs that were in the selected range have gone.
Code Breakdown:
Sub ChangeNegativetoPositive()
For Each Cell In Selection
If Cell.Value < 0 Then
Cell.Value = -Cell.Value
End If
Next Cell
End Sub
As we have said before, you don’t need any coding knowledge to do this. But if you want to know what this code does then the explanation is simple:
The code uses the For loop to check if the cell value is bigger or smaller than 0. If the cell value is less than 0, or the number is negative, the code multiplies the value with a negative. This returns a positive value and the code repeats for the next cells. This removes all the negative signs from the selected range.
Although using VBA will help you remove the negative signs in a few clicks, after using VBA, all history of your previous datasheet is lost. That is you cannot undo the change. This is one major drawback of the function. So it is better to use functions in this case. But the VBA function can come quite handy if you regularly work with negative signs.
Using Flash Fill
Another way to remove the negative signs from your worksheet is to use the Flash fill method. It is one of Excel’s new smart tools and was introduced in 2013. So you can only use this if you have Excel 13 or later.
The Flash fill function quickly identifies patterns and reuses them in your dataset. This is similar to what we have done in the previous methods, but instead of typing formulas, you can just let Excel do the hard work.
Let’s see the process:
1. First, type in some of your expected results in the adjacent cells of your datasheet. For example, in our case, we will type 110 for the first cell and 57 in the cell below and press enter.
2. Now just press CTRL and E together and Excel will fill up all the remaining cells with negative signs removed from the numbers.
3. Now, as seen in the picture just click on the icon marked 1 which is the Flash fill icon, and choose to accept suggestions. And you are done.
The flash fill method is one of the quickest methods to remove the negative signs from your dataset. With this method, you can let Excel do the heavy lifting and just simply accept the changes that Excel suggests.
But, this method requires a pattern for Excel. So you have to type in at least one number where you have changed a negative into a positive for Excel to guess what you want. But overall it is a handy tool if you want to remove negative signs quickly.
Ending Thoughts
In this guide, we have shown you 6 handy methods to remove negative signs from numbers in Excel. We chose to keep the sample datasheet simple for your ease. We discussed different functions to remove negative signs directly without touching the positive numbers. If you wanted to alter the positive and negative numbers directly, we also discussed that with the paste special method.
Finally, we discussed some quick and smart ways to remove negative numbers while ignoring the positive numbers with Flash fill and VBA. Although the methods may have their drawbacks, each of the methods has its own merits as well. We suggest that you look at all of the methods we discussed in this guide to use in different scenarios.
Similar Post: