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.


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.

=ABS(B5)

Cell B5 contains a negative number -7. The result is 7 as the ABS function removed the negative sign from it.

Remove Negative Sign in Excel

Now using the Fill Handler, we can copy and paste the formula for cells C6:C10.

Remove Negative 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.

Remove Negative Sign in Excel

  • Go to Find & Select tab to choose the Replace.

Remove Negative Sign in Excel

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

Remove Negative Sign in Excel

Formula Breakdown
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.


Similar Readings


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

Remove Negative Sign in Excel

  • Now, copy cell C5 by pressing Ctrl + C and select cells B5:B10 by dragging the mouse.

Remove Negative Sign in Excel

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

Remove Negative Sign in Excel


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

  • Here we have a bunch of negative numbers in cells B5:B10.

Remove Negative Sign in Excel

  • Put the positive number of cells B5(-7) that is 7 in cell.

Remove Negative Sign in Excel

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

Remove Negative Sign in Excel

  • Now in the output, we see numbers without the negative signs.

Read More: How to Remove Formatting in Excel Without Removing Contents


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.

Remove Negative Sign in Excel

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

Remove Negative Sign in Excel

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.

Read More: How to Delete Blank Cells and Shift Data Left in Excel (3 Methods)


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.

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

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo