Changing the sign of a number

Topic

New Reply

How can I change the sign of a number in Excel? I could swear I have put a +/- button on the toolbar before, that you could click to change the sign of number in the selected cell. But I can’t find it now. . . .

Reply | Quote

Replies

You could do it with a macro and assign it to a custom button:

Sub Invert()
On Error GoTo last
ActiveCell = 0 – ActiveCell
last:
End Sub

Reply | Quote

My VBA programming is rusty, so I haven’t tried that yet. I do know that you would have to check for three different cases — if the number is > 0, < 0, or 0.

Thanks for the hint about "ActiveCell", I didn't know about that….

I thought there was already a button defined internally, but I can't find one.

Reply | Quote

It depends on where and how you want to do it. If the number is in A1, then you can put the following formula in the cell where you want the number with the inverted sign:

=A1*-1

If you have a range of cells where you want to change the sign in the cell, then you can follow this procedure:

1- Find an empty cell somewhere on the spreadsheet and enter -1 into the cell.
2- Select the range of cells that you want to change the sign in.
3- In the Edit menu select “Paste Special”.
4- In the dialong box, in the “Operation” group, select “Multiply”.
5- Press OK

All the selected cells should have the sign changed.

If you want to do it in VBA code, then just multiply by -1.

Do any of these meet what you want to do?

Reply | Quote

The Paste Special will work great. No programming either; easier to pass on to an end user.

(Just need to remember to Edit, Copy the -1 cell before proceeding to Step 2.)

Thanks!

Reply | Quote