# change positive numbers to negative

#### learning_grexcel

##### Active Member
I have many columns with numbers in many sheets. There are certain columns, certain ranges which I want to make negative with (-) sign. For e.g. 1 should be changed to -1, 535 should be -535, etc.

Right now I'm doing it manually. Is it possible to be done with any simple steps?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Scott Huish

##### MrExcel MVP
Put -1 in a blank cell.
Copy this cell.
Select your range that you want to convert.
Right click.
Click on Paste Special. Under Operation. Click on Multiply.
Click OK.

#### Rick Rothstein

##### MrExcel MVP
Enter -1 in any cell, then copy that cell, then select all the cells you want to make into minus values, then keyin ALT+es (to bring up the Paste Special dialog box) and select "Multiply" from the "Operation" section and click the OK button. You can now clear the cell with the -1 that you entered.

#### Jonmo1

##### MrExcel MVP
Are there any negative numbers already there that need to remain negative?

#### learning_grexcel

##### Active Member
Thanks to every one. It worked for me.

However, as Jonmo1 pointed out, there were a few figures which were already negative which I changed it manually. I would like to know if there is a short way of doing it also.

#### nuked

##### Well-known Member
You could add a column with the following formula copied down:

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

#### Rick Rothstein

##### MrExcel MVP
However, as Jonmo1 pointed out, there were a few figures which were already negative which I changed it manually. I would like to know if there is a short way of doing it also.
You should have mentioned that to us in your original message. Select the cells, press CTRL+H to bring up the Replace dialog box, type a minus sign into the "Find what" field, leave the "Replace with" field empty, click the "Options>>" button and make sure there is no checkmark in the "Match entire cell contents" checkbox and then click the "Replace All" button. Now follow the steps that HOTPEPPER or I laid out for you in either Message #2 or #3.

#### Scott Huish

##### MrExcel MVP
You could add a column with the following formula copied down:

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

If you are going to use a formula, this is shorter: =-ABS(A1)

Replies
0
Views
475
Replies
3
Views
961
Replies
14
Views
295
Replies
3
Views
87
Replies
6
Views
375

1,195,670
Messages
6,011,074
Members
441,580
Latest member
BornholmerBjarne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back