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:

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.

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.

### 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:

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:

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

- Type -1 in any blank cell of the worksheet. For our example, let us put it in cell C2:

- Press CTRL+C on your keyboard to copy this value.
- Select the range of cells containing numbers that you want to make negative.

- Right-click on your selection and click on
*Paste Special*from the context menu that appears.

- This displays the
*Paste Special*dialog box, which gives you different paste options to choose from.

- Under the
*Operation*category, check the radio button next to*‘Multiply’*.

- Click OK.
- All your selected cells should now have their signs reversed to negative.

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

- Navigate to
*Developer*->*Visual Basic*from your menu ribbon. - This opens the VBA window. Click
*Insert*->*Module*. - Copy the above code and paste it into the module window.

- Your code is now ready to run. Close the VBA window.
- Go to your worksheet and select the range of cells that you want to work with (cells A2:A8 in our example).
- Navigate to
*Macros*->*Developer*. - This will open the
*Macros*dialog box. Select the macro named*MakeNegative*from the list of macros displayed.

- Click
*Run*.

Your code should now run and you should see all the positive numbers in your selected range 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:**