This article explains how to remove negative sign in Excel using 7 different methods explained with examples. It happens to many calculations where we need to use just the values of numbers irrespective of their being positive or negative. These methods are easy to apply to get the desired results.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
7 Methods to Remove Negative Sign in Excel
1. Remove Negative Sign in Excel Using the ABS Function
We can use the ABS Function to get the absolute value of numbers. The function takes a number as its only argument and returns only the value irrespective of its sign.
Here, we have a list of numbers in cells B4:B10 with both positive and negative. In cell C5, we put the following formula.
Cell B5 contains a negative number -7. The result is 7 as the ABS function removed the negative sign from it.
Now using the Fill Handler, we can copy and paste the formula for cells C6:C10.
2. Find and Replace Negative Sign in Excel
Excel’s find and replace feature allows us to replace a string or number quickly and efficiently. In this example, we’re going to replace the negative signs with an empty string. Follow the steps described below:
- We have three negative numbers in our dataset.
- Go to Find & Select tab to choose the Replace.
- In the Find and Replace window put a minus (-) sign in the Find what input box and leave the Replace with input box blank. After that click the Replace All.
- The above steps replaced the negative signs with an empty string. So, we’ve removed the negative signs from the numbers. We’ve also received a confirmation that 3 replacements are done. Finally, close the Find and Replace window by clicking the Close.
3. Use the IF Function to Check and Remove Negative Sign in Excel
The use of the IF function gives the facility to first check whether a number is negative or not and then put the necessary logic to remove the negative sign. In this example, in cell C5, write the following formula.
=IF(B5<0, -B5, B5)
The output in cell C5 is negative sign removed value 7.
As we know the syntax of the IF function is:
=IF(logical_test, [value_if_true], [value_if_false])
In our formula,
logical_test = B5<0, checks whether the value of cell B5 is less than zero or not
[value_if_true] = -B5, if the number is less than zero i.e., +negative then multiply it with a negative sign so that it becomes positive.
[value_if_false] = B5, if the number is not less than zero then keep the number as it is.
We can now copy the formula to the other cells by using the Fill Handle to remove the negative sign from the negative numbers.
- How to Remove #DIV/0! Error in Excel (5 Methods)
- Remove Panes in Excel (4 Methods)
- How to Remove Hyperlink from Excel (7 Methods)
- Remove Comments in Excel (7 Quick Methods)
- How to Remove Outliers in Excel (3 Ways)
4. Reverse Negative Sign to Positive with the Paste Special Multiplication
In this example, we’ll take the help of value multiplication from the Paste Special options to remove negative signs from negative numbers. Let’s follow the steps below to perform that.
- In cell C5, put -1 that will be used to multiply with the negative numbers in cells B5:B10.
Note: You can put the -1 in any cell in the worksheet.
- Now, copy cell C5 by pressing Ctrl + C and select cells B5:B10 by dragging the mouse.
- After that, right-click on any of the selected cells B5:B10 and click the Paste Special.
- Now, in the Paste Special window select Values from the Paste options and Multiply from the Operation options. Finally hit OK to save the changes.
- Now, we see the final result, numbers without the negative signs.
5. Remove Negative Sign in Excel Using the Flash Fill
Flash Fill can automatically sense the data pattern and fills cells with data according to that pattern. We’re going to use this great feature from Excel to remove negative signs from the negative numbers. Let’s follow along!
- Here we have a bunch of negative numbers in cells B5:B10.
- Put the positive number of cells B5(-7) that is 7 in cell.
- Then in cell C6, press Ctrl + E.
- The above steps flash filled cells C6:C10 with numbers that are negative signs removed. To complete the process, click on the small icon right to the flash-filled cells and click on Accept suggestions.
6. Add Custom Formatting to Remove Negative Sign
Adding custom formatting is also very useful in case we want to remove the negative signs from numbers. Let’s add some custom formatting to our dataset. We need to follow the steps below.
- Select cells B5:B10 that contain negative numbers.
- Press Ctrl + 1 to open up the Format Cells.
- In the Format Cells window, click the Custom option from the Category list under the Number tab.
- In the Type input box write #,###, #,### number format code. And then hit Enter.
- Now in the output, we see numbers without the negative signs.
7. Apply VBA Code to Remove Negative Sign from Selected Cells
Using VBA is always quick and easy to accomplish a task in Excel. We can also run a simple VBA code to do our job in this article. Let’s remove negative signs from the selected cells in this example.
- At first, select the cells B5:B10 that contain the negative numbers.
- Then from the Developer Tab click on the Visual Basic option. This will open up the Visual Basic Editor.
- Now, click on the Insert Tab to choose Module. It’s going to open up a new module to write code.
- Finally, copy the following code and press F5 to run.
Sub RemoveNegativeSign() For Each Cell In Selection If Cell.Value < 0 Then Cell.Value = -Cell.Value End If Next Cell End Sub
In the VBA code, the For Each loop is going to apply the If…Then…End If condition to each of the cells of B5:B10. It checks whether the number is less than zero or not. If the logic is true, it’ll replace the cell value with the negative value of itself. As a result, it’ll turn a positive number. That’s how the negative sign is removed.
Now we can see the result after running the VBA code.
Sub RemoveNegativeSign() Dim SelectedCells As Range For Each SelectedCells In Selection If SelectedCells.Value <> "" Then If IsNumeric(SelectedCells.Value) Then SelectedCells.Value = Abs(SelectedCells.Value) End If End If Next SelectedCells End Sub
This code used the ABS function to get only the values of the selected cells.
Things to Remember
- Although using VBA code is great to accomplish our goal. But once the code is run, we lost the history. It means we cannot undo the change anymore.
- In case we need to change our source data from time to time, it’s better to use methods that use functions like the ABS function. In this case, the output is dynamic with the change of the source data.
Now, we know several methods to remove negative signs in Excel. Hopefully, it would encourage you to use these methods in your calculations more confidently. Any questions or suggestions don’t forget to put them in the comment box below.