change positive numbers to negative

learning_grexcel

Active Member
Joined
Jan 29, 2011
Messages
319
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You could add a column with the following formula copied down:

=IF(A1>0, A1*-1,A1)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top