Remove Negative Sign in Excel

Are you Working on an Excel spreadsheet and wondering how to get rid of negative signs?

I often need to do this when working on accounting data, and expenses are often shown with a negative sign. The first thing I need to do is to remove those negative/minus signs.

And it’s fairly easy to do in Excel (in more than one way).

In this article, I will show you some simple methods that you can use to remove the negative sign in Excel.

Method 1: Using ABS Function to Remove Negative Signs

Excel is fully loaded with many functions to do our tasks very easily. When you want to remove negative signs, you can easily use the Excel ABS function.

Below, I have a data set where column A shows the original data value.

Now, I want to take the absolute value of column A values to column B. To do that, I can use the below formula.

=ABS(A2)

The syntax of the ABS function is ABS(number). This function has only one argument, and that is number.

The number means the original value of which you want to take the absolute value. So, in this case, I have selected cell A2. You can even type the original number directly into the argument.

You can also use this formula as an array formula. You can use the below array formula.

=ABS(A2:A6)

The advantage of using the array formula is that you don’t need to copy the formula to the below cells manually.

If you are using the array formula, make sure to keep the spill range blank. Otherwise, you’ll get a #SPILL! Error.

Also read: How to Make Positive Numbers Negative in Excel?

Method 2: Using IF Function to Remove Negative Signs

To remove negative signs in Excel, you can use the Excel IF function.

Below, I have a data set where column A shows the original data value.

Now, I want to take the absolute value of column A values to column B. To do that, I can use the below formula.

=IF(A2<0,A2*-1,A2)

The syntax of the IF function is IF(logical_test, value_if_true, [value_if_false]).

• Logical_test – This is the condition that you need to test. In this case, you have to test whether the number is negative. In other words, you have to test whether the number is less than zero. So, you can enter A2<0 as the logical test.
• Value_if_true – For this argument, you have to enter what you want to return if the logical test is TRUE. In this example, if the value is true (the number is negative), you want to multiply the number by minus one (-1) to convert it to a positive number. So, you can enter A2*-1 for this argument.
• [value_if_false] – For this last argument, you have to enter what you want to return if the logical test is FALSE.  In this example, if the value is false (the number is not negative), you want to take the exact number in column A. So, you can enter A2 for this argument.

Also read: How to Count Negative Numbers in Excel (Formula)

Method 3: Using SUBSTITUTE Function to Remove Negative Signs

Another helpful function that you can use to remove negative signs in Excel is the SUBSTITUTE function.

Below, I have a data set where column A shows the original data value.

Now, I want to take the absolute value of column A values to column B. To do that, I can use the below formula.

=SUBSTITUTE(A2,"-","")

The syntax of the SUBSTITUTE function is SUBSTITUTE(text, old_text, new_text, [instance_num]).

• text – For this argument, you have to select the number in column A. So, you can select cell A2.
• old_text – For this argument, you have to enter the text that you want to replace. In this case, you want to replace the negative sign. You have to enter it within double quotes. “-”.
• new_text – This is the text that you want to enter in place of the given old text. In this case, you just want to remove the negative sign, and you don’t need to add any new sign or text for that. So, you can enter just two double quotes (“”).

The last argument is optional, and you can ignore it for this example.

Using this function, you’ll get the result in a text format.

For example, when you select all the results of the SUBSTITUTE function, you’ll see only the count in the Status Bar, and you can’t get the sum of the selected values.

The solution for this issue is to combine the SUBSTITUTE function with the VALUE function. Then, the formula is as follows.

=VALUE(SUBSTITUTE(A2,"-",""))

You can use the SUBSTITUTE function as an array function too. Then, the formula is;

=VALUE(SUBSTITUTE(A2:A6,"-",""))

The advantage of using the array formula is that you don’t need to copy the formula to the below cells manually.

If you are using the array formula, make sure to keep the spill range blank. Otherwise, you’ll get a #SPILL! Error.

Also read: Show Negative Numbers in Parentheses/Brackets in Excel

Method 4: Using Number Formatting to Hide Negative Signs

In the all above methods, you have extracted the numbers without the negative signs to a new column.

But, there are situations where you have to remove negative signs of the numbers without extracting them to a new column.

In such situations, you can use the Excel number formatting to remove negative signs.

Below, I have a data set where column A shows some numbers that have a negative sign that I want to remove.

Now, I want to remove the negative signs of numbers while keeping the numbers in the same column. To do that, I can follow the below simple steps.

1. Select all the numbers. In this example, you can select cells A2 to A6.

2. Press Ctrl + 1 to open the Format Cells dialog box.

If you don’t like to use shortcuts, you can go to the Home tab and click the expansion icon of the Number format group to open the “Format Cells” dialog box.

3. Go to the “Number” tab of the “Format cells” dialog box and select “Custom” as the category.

4. If the current Type is “General”, paste the below formatting code in the Type box.

0;0

If there is any other custom number format, you have to just copy the same code before the first semicolon and paste it after the semicolon.

If there is no semicolon in the number format code, copy the existing number format code, enter a semicolon, and then paste the copied number format code.

5. Click the “OK” button.

Then, Excel removes the negative signs of the selected cells.

Note: One important thing to know about this method is that it does not change the value in the cell. As you can see in the image above, the value in the formula bar is still -25, but in the cell in the worksheet, it shows 25. This method allows us to change the way the number is being shown in the cell

Also read: How to Sum Positive Numbers in Excel (Formula)

Method 5: Using Find and Replace to Remove Negative Signs

When you have to remove negative signs of the numbers while keeping the numbers in the same column, you can use the Excel Find and Replace tool.

Below, I have a data set where column A shows some numbers that have a negative sign that I want to remove.

Now, let me show you how to use Find and Replace to remove the negative sign from the numbers.

1. Select all the numbers. In this example, you can select cells A2 to A6.

2. Press Ctrl + H to open the “Find & Replace” dialog box.

If you don’t like to use the shortcut, first go to the Home tab, expand the “Find & Select” options of the “Editing” group, and select “Replace”.

3. Enter a minus (-) sign in the “Find what” box.

4. Click the “Replace All” button.

Now, all the negative signs are removed from the selected cells.

Also read: Remove Parentheses (Brackets) in Excel

Method 6: Using Flash Fill to Get Rid of Negative Signs

When you want to extract absolute values of numbers to another column without using Excel functions, you can use the Excel Flash Fill feature.

Below, I have a data set where column A shows some numbers that have a negative sign that I want to remove.

Now, let me show you how to use Flash Fill to remove the negative/minus signs. The good thing about this method is that it does use any formulas to get this done, and it quite fast and easy.

1. Enter the absolute value of the first number in the adjacent column. So, here, you can enter 25 in cell B2.

2. Press “Ctrl + E” to apply the Flash Fill.

If you don’t want to use the Excel shortcut, you can go to the “Data” tab and click the “Flash Fill” icon in the “Data Tools” group.

If the first original number is not negative, you have to enter the absolute values of numbers until you row that has the first negative original number. This is because Flash Fill needs to see the pattern that you want it to follow.

Also read: How to Remove Dollar Sign in Excel

Method 7: Using a Simple VBA Code to Remove Negative Signs

When you have to remove negative signs of the numbers while keeping the numbers in the same column repeatedly, the best method is to use an Excel VBA code.

Below, I have a data set where column A shows the original data value.

Now, let me show you a simple VBA code to remove all the negative signs as soon as the code is run.

You can follow the below steps for this.

1. Press Alt + F11 to open the Excel VBA Editor window. If the function keys are locked, you have to use Alt + Fn + F11.

2. Click “Insert” and select “Module”.

3. Enter the below VBA code in the new Module

Sub Remove_Negative_Sign()

    ' Declare a variable for iteration over cells
    Dim Cell As Range

    ' Loop through each cell in the selected range
    For Each Cell In Selection
        ' Change the cell value to its absolute value (removing any negative sign)
        Cell.Value = Abs(Cell.Value)
    Next Cell

End Sub

4. Now, you can select the cells where you want to remove negative signs and click the “Run” button.

After you run the VBA code, Excel removes all the negative signs from the selected cells.                

Explanation of the code: This VBA macro iterates through each cell in the currently selected range. For each cell, it uses the Abs function to convert the value to its absolute value. Do note that if any cells in the selection contain non-numeric values, those cells will remain unchanged as the Abs function only affects numbers.

To save the VBA Code, you have to save your workbook as an Excel Macro-Enabled Workbook (*.xlsm).

It is important to note that actions that you perform using VBA codes are irreversible. So, it is good to keep a copy of your original data before you run the VBA code in case you want the original data.

There are a few other methods in Excel to remove negative signs in Excel such as using the multiply option of the Paste Special feature. You can select the best method that works for you.

Other Excel articles you may also like:

• Round Up to the Nearest Whole Number in Excel (Formulas)
• How to Replace Zero with Dash in Excel?
• How to Remove Dashes (-) in Excel?