How to Remove Negative Sign in Excel (7 Methods)

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.

Remove Negative Sign in Excel: 7 Methods

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.

Read More: How to Remove Plus Sign in Excel

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:

Steps:

• 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 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.

Read More: How to Remove Sign from Numbers in Excel

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.

The output in cell C5 is negative sign removed value 7.

We can now copy the formula to the other cells by using the Fill Handle to remove the negative sign from the negative numbers.

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.
Steps:

• In cell C5, put -1 which 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 fill 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!
Steps:

• 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.

Steps:

• 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.

Steps:

• 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

Explanation:
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.

Alternative 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 for accomplishing our goal. But once the code is run, we lose 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.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

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.

Related Articles

• How to Remove Currency Symbol in Excel
• How to Remove Dollar Sign in Excel
• How to Remove Dollar Sign in Excel Formula
• How to Remove Pound Sign in Excel
• How to Remove Percentage Symbol in Excel
• How to Remove Percentage Symbol in Excel Without Changing Values

<< Go Back to Remove Symbol in Excel | Excel Symbols | Learn Excel