How to Make Positive Numbers Negative in Excel?

by

Changing a positive number to a negative should really be easy right? After all, it only requires you to add a minus sign before the number.

But what if you have a whole list of positive numbers that you need to convert to negative?

Manually adding a minus sign to each number would be really tiring, and honestly, quite inefficient. So we need some sort of trick that can help us change all the numbers to negative in one go. 

Now although Excel does not have a specific feature or menu item to help us do that, we can use a few simple tricks to quickly make all positive numbers negative.

In this tutorial we will show you three such ways:

  • Using the Paste Special feature
  • Using a Formula
  • Using VBA

Let us go over each of these methods one by one.

Using Formula to Make Positive Numbers Negative

The first method (the Paste Special method) basically reverses the sign of numbers, so if you have positive numbers they will get converted to negative and vice-versa.

However, if you want to ensure that only the positive numbers get converted to negative while the negative numbers remain the same, you can opt for the method discussed in this section.

This method uses a simple formula to convert the positive numbers in a list to negative. Let’s say you have the following list of numbers:

Data with positive and negative numbers

Notice that this time the list has a mix of both positive and negative numbers.

We want to only make the positive numbers negative but keep the negative numbers as they are.

There are two different formulas that you can use to solve this problem, and both are equally correct and useful.

Formula 1: Using the IF Function

The first formula uses the IF function. Type the following formula in cell B2, and press the return key:

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

Then, copy this formula down to the rest of the cells in the column using the fill handle. You should now get all negative numbers in column B.

IF formula to convert positive to negative

The formula simply checks if the value in the corresponding cell of column A is more than 0 (or a positive number).

If it is a positive number, the formula will return the value obtained by multiplying it with -1 (the negative equivalent of the number).

If it is 0 or a negative number, however, then the formula returns the same value as it is.

Also read: Remove Negative Sign in Excel

Formula 2: Using the ABS Function

The second formula uses the ABS function. This function is used to find the absolute value of a given number.

By absolute value, we mean just the value, without considering the number’s positive or negative sign.

So to convert the value in cell A2, type in the following formula in cell B2, and press the return key:

=ABS(A2)*-1

Then, copy this formula down to the rest of the cells in the column using the fill handle. You should now get all negative numbers in column B.

Since the ABS function simply takes the absolute value of a given number, it returns only the number, irrespective of whether it is negative or positive.

The above formula then multiplies this value by -1 allowing you to express it in negative form. The result that you get is shown below:

ABS function to convert negative to positive

Once you get all your results, you can copy the resulting values from column B (by using the keyboard shortcut CTRL+C) and then Paste as Value over column A (by using the keyboard shortcut CTRL+Shift+V) to write over the original values. You can then go ahead and delete column B.

Using Excel’s Paste Special Feature to Make Positive Numbers Negative

This method is useful if all your numbers are positive and you want to change the signs of all of them to negative.

Let’s say you have the following list of positive numbers:

Dataset with positive numbers

Here are the steps that you need to follow if you want to make all the above numbers negative using the Paste Special feature:

  1. Type -1 in any blank cell of the worksheet. For our example, let us put it in cell C2:
Enter -1 in an empty cells
  1. Press CTRL+C on your keyboard to copy this value.
  2. Select the range of cells containing numbers that you want to make negative.
Select the positive numbers
  1. Right-click on your selection and click on Paste Special from the context menu that appears.
Click on Paste Special
  1. This displays the Paste Special dialog box, which gives you different paste options to choose from.
  1. Under the Operation category, check the radio button next to ‘Multiply’.
Click on Multiply
  1. Click OK.
  2. All your selected cells should now have their signs reversed to negative.
All numbers converted to negative numbets
  1. You can now go ahead and remove the value -1 from cell C2. 

How did this happen?

The Paste Special feature’s ‘Multiply’ option multiplies your copied values with the values in your target cell(s). By ‘target cells’, we mean the cells that you want to paste over.

Since we had copied the value ‘-1’ before the Paste Special operation, all our selected cells simply got multiplied with the copied value (-1). This made all the values negative.

Note: This method simply multiplies the selected values by -1. So if you had an already negative value in your list, then this method would simply make that number positive.

Using VBA to Make Positive Numbers Negative

The third method uses VBA code to programmatically make a range of numbers negative. Here’s the VBA code that you can use:

'Code developed by Steve Scott from spreadsheetplanet.com
Sub MakeNegative()
Dim ws As Worksheet
Dim rng As Range
Dim result As Range
Set ws = Application.ActiveSheet
Set rng = Application.Selection
For Each cell In rng
If cell.Value > 0 Then
cell.Value = cell.Value * -1
End If
Next
End Sub

This code takes a selected range of cells and then checks each cell to see if it is a positive number.

If it’s positive, the code converts the number to negative by multiplying it with -1. If the number is not positive, the code leaves it as it is.

To apply this script, to your dataset, follow these steps:

  1. Navigate to Developer->Visual Basic from your menu ribbon.
  2. This opens the VBA window. Click Insert->Module.
  3. Copy the above code and paste it into the module window.
Copy-paste code in the VBA moduke
  1. Your code is now ready to run. Close the VBA window.
  2. Go to your worksheet and select the range of cells that you want to work with (cells A2:A8 in our example).
  3. Navigate to Macros->Developer.
  4. This will open the Macros dialog box. Select the macro named MakeNegative from the list of macros displayed.
Run the make negative macro
  1. Click Run.

Your code should now run and you should see all the positive numbers in your selected range converted to negative.

All positive numbers are converted to negative

Note that this method permanently changes the values in your worksheet and you can’t undo the changes made by the script.

We thus suggest you keep a backup of your data before running the script.

In this tutorial, we showed you three easy ways to make positive numbers negative in Excel.

The Paste Special method works great if you want to simply reverse the signs of all your numbers.

However, if you only want to convert the positive numbers to negative (leaving the negative numbers as they are), then using the formula method and the VBA method might work better for you.

Also note that the same techniques will also work if you want to do the opposite, i.e., change negative numbers to positive. You will just have to adjust the method/formulas accordingly.

We hope this tutorial was helpful for you and easy to follow.

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment