The Question: James asks how to add a decimal before all of the numbers in a range; i.e. how to convert 42 to 0.42
Today, in Episode #1348, Bill shows us how to get the proper value.
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
Today, in Episode #1348, Bill shows us how to get the proper value.
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1348: Add Decimal Before.
Well hey, welcome back to the MrExcel netcast and I'm Bill Jelen.
Today's question is sent in by James.
James has a huge data set here with all these numbers like 21, 39, 55 and he realized that the person who provided that data really meant like percentages.
Like 21% and so he needs to add a decimal before, you know.
Basically I come in here, F2 to edit, press the Home key then type the decimal and press Enter but he has hundreds of these.
Is there a faster way to do that?
Luckily there is.
We're just going to go to any blank cell anywhere in the spreadsheet.
I'm going use cell A1 up here and type 0.01.
In essence we want to take that 1/100 of a percent or 1/100 of a 0.01 and you want to take that number and multiply it by all the other numbers in the data set.
So, I'm going to copy that data and use the copy icon up here and then I'm going to select all the numbers that are a hundred to large and we're going go into paste special; back in Excel 2003 edit paste special.
In Excel 2007 open the paste drop-down and go all the way down to the bottom for paste special.
In Excel 2010, still, ignore all those other icons and come down here, paste special and what we're going do is we're going to take the point 0.1 that's on the clipboard and multiply that number times all of the items in our selection.
Click OK and right there we instantly have everything multiplied by point 0.01.
Great way to very quickly solve this type of a problem.
Well hey, I want to thank everyone for stopping by and we’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1348: Add Decimal Before.
Well hey, welcome back to the MrExcel netcast and I'm Bill Jelen.
Today's question is sent in by James.
James has a huge data set here with all these numbers like 21, 39, 55 and he realized that the person who provided that data really meant like percentages.
Like 21% and so he needs to add a decimal before, you know.
Basically I come in here, F2 to edit, press the Home key then type the decimal and press Enter but he has hundreds of these.
Is there a faster way to do that?
Luckily there is.
We're just going to go to any blank cell anywhere in the spreadsheet.
I'm going use cell A1 up here and type 0.01.
In essence we want to take that 1/100 of a percent or 1/100 of a 0.01 and you want to take that number and multiply it by all the other numbers in the data set.
So, I'm going to copy that data and use the copy icon up here and then I'm going to select all the numbers that are a hundred to large and we're going go into paste special; back in Excel 2003 edit paste special.
In Excel 2007 open the paste drop-down and go all the way down to the bottom for paste special.
In Excel 2010, still, ignore all those other icons and come down here, paste special and what we're going do is we're going to take the point 0.1 that's on the clipboard and multiply that number times all of the items in our selection.
Click OK and right there we instantly have everything multiplied by point 0.01.
Great way to very quickly solve this type of a problem.
Well hey, I want to thank everyone for stopping by and we’ll see you next time for another netcast from MrExcel.
