Simon from the UK sends in a question. He has a range of negative numbers that he needs to convert to positive values. Episode 549 shows you two methods for converting the values to positive.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Today we have a question sent in by Simon in the UK.
Simon has a large range of negative numbers and he needs to convert all of those negative numbers to be positive, and he said he has an add-in that he uses to convert that.
But, wonders if there's any way natively in Excel to convert negative numbers deposit.
Well my method is that I go to a blank cell, so I'll just go to any blank cell and put in the number -1 Enter that cell, copy the cell to the clipboard, I use CTRL C to copy and then select my range and we'll use Edit, Paste Special, so that we want to use values and multiply.
Basically the important part here is we're going to multiply the -1 on the clipboard by the entire range.
The reason that we use values is that we don't destroy the number formatting if we just used all, it would take any number formatting from basically some F1 and overwrite all the other number formatting.
So I'll click OK and that quickly all of the negative values change to positive.
Now, one other method that might work and this is particularly important if you already have some positives in there, and you need to make sure that everything is positive, on what you can do then is select your cells and you'll use Edit, Replace, and say we want to convert every occurrence of a minus sign to nothing.
In the options we want to make sure that we're looking at formulas and also that match entire cell contents is not checked.
We'll click replace all and now all of our negative numbers have changed to positive numbers, the positive numbers stayed as positive numbers.
So depending on what you need to do, you can either use the Edit Paste Special values multiply trick or just Edit Replace to change all minus signs to nothing.
Hey thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Today we have a question sent in by Simon in the UK.
Simon has a large range of negative numbers and he needs to convert all of those negative numbers to be positive, and he said he has an add-in that he uses to convert that.
But, wonders if there's any way natively in Excel to convert negative numbers deposit.
Well my method is that I go to a blank cell, so I'll just go to any blank cell and put in the number -1 Enter that cell, copy the cell to the clipboard, I use CTRL C to copy and then select my range and we'll use Edit, Paste Special, so that we want to use values and multiply.
Basically the important part here is we're going to multiply the -1 on the clipboard by the entire range.
The reason that we use values is that we don't destroy the number formatting if we just used all, it would take any number formatting from basically some F1 and overwrite all the other number formatting.
So I'll click OK and that quickly all of the negative values change to positive.
Now, one other method that might work and this is particularly important if you already have some positives in there, and you need to make sure that everything is positive, on what you can do then is select your cells and you'll use Edit, Replace, and say we want to convert every occurrence of a minus sign to nothing.
In the options we want to make sure that we're looking at formulas and also that match entire cell contents is not checked.
We'll click replace all and now all of our negative numbers have changed to positive numbers, the positive numbers stayed as positive numbers.
So depending on what you need to do, you can either use the Edit Paste Special values multiply trick or just Edit Replace to change all minus signs to nothing.
Hey thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
