2 Ways to Reverse the Number Sign Positive Negative in Excel

The Opinion Lighthouse
32 followers
8 Video Views·Nov 8, 2022
2 Ways to Reverse the Number Sign Positive Negative in Excel
Welcome to Excel Campus. My name is John and in this video I'm going to explain two different
ways to reverse the number sign from positive to negative or negative to positive. So here I have a
simple example of a journal entry with some debits here in the left column and credits in the right
and we want to reverse the signs for all these different numbers here from negative to positive
and then the credit column we might want to reverse these from positive to negative. So
there's a few different ways to do this in Excel. The first way is to just multiply just to create
a formula and multiply this number by negative one. So in cell D2 here we could just type equal sign
and then select this cell right here B2 and then multiply by negative one and that will change the
sign from negative to positive and that would work as well from changing a positive number to a
negative number. So here we go equals and then multiply that by negative one and same thing there.
So we could copy those formulas down and across to change all of the signs. Now if we didn't want
to create a new formula with new columns with these results and instead we just want to change
these existing numbers here and in columns B and C we can do that with the paste special command.
So to do that we're just going to first put a negative one. I'm just going to type negative one
here in this cell. It could be any blank cell so it'll be cell E2 and we're going to copy that. So
control C or right click copy. We'll see that's now copied to the clipboard and now we're just
going to select first we'll just select this single cell here and we're going to do a paste special.
So right click paste special or alt E S on the keyboard will bring up this menu here and then
we're going to choose this multiply operation. So what this is going to do is it's going to take
the value that's currently copied to the clipboard which is negative one and it's going to do a paste
operation to multiply the selection of cells that we currently have selected and multiply those by
negative one. So we only have this cell selected right now so if I hit okay that will do that
multiplication. We can see now this cell is changed to positive number. So what if we wanted to apply
this to all of the cells here all of these cells? Well we can do that as well. I'm going to go ahead
undo that last action so we now have a negative here and we still have this copied to the clipboard
so all we need to do is we can select all these cells here do the same thing just right click
copy paste special I'm sorry right click paste special go to multiply and then hit okay and you
can see that that's done the multiplication on all the cells and reversed all the signs.
Now it's also created these zeros here because any blank cells it's multiplying those by negative one
and it just basically says zero times negative one equals zero and we might not want those zeros
right there. So again I'm going to undo this and to fix that we can first select all the cells that
just contain values and we can do that with the go to special menu. So on the home tab of the ribbon
if we go over here to find and select there's a go to special option. A keyboard shortcut here would
be F5 and then hitting the special button so we could also just press F5 on the keyboard and then
the special button and that'll bring up this go to special menu and then from there we just want
to choose constants. So this constants is just going to select any cells that contain values.
If these contain formulas you could choose formulas instead but these cells here just
contain values so for that we're going to choose constants and then click okay and now we can see
that only the cells have been selected that contain values and now we can do a right click
paste special again and choose multiply and we'll hit okay and that will do that multiplication to
only the cells now that are selected. So that looks a lot nicer it preserves our original
formatting here with these values in each column and we've just reversed the sign in each for each
cell by multiplying it by negative one using that paste special command. So that's probably
the ultimate solution for this kind of journal entry scenario. Now we can also apply this same
technique to cells that contain formulas. So on this sheet here I have a similar example but in
this case these cells contain formulas. They contain this VLOOKUP formula and we might want
to reverse this sign but also retain or keep this formula in the cell. We don't necessarily want to
change this to values or mess this up so we can use the paste special for this as well. So I'm going
to put a negative one here in cell D2 and then again I'm just going to copy it ctrl c or right
click copy and we'll select all of these cells right here and then right click paste special
and we'll choose multiply and then hit okay and we can see that's reversed all the signs but if we
look at one of these formulas here we can see that the VLOOKUP formula has been retained
and a multiplication symbol times negative one has been added to the formula for us. So the paste
special command actually adds that times negative one to the formula for us so it retains our
formulas. So it's a great feature there as well using that paste special command. So I hope that
helps. Of course if you have any questions at all please leave a comment below and I'll be happy to
help answer them. If you enjoyed that video there are a few simple things you can do to help me out.
If you are watching this video on YouTube click the like button below the video
and leave a comment with any questions or feedback and please don't forget to subscribe
to my free email newsletter to get more tips and tricks that will help you learn Excel.
Thanks again for watching and I'll see you soon.
