Jim from Timken shares a trick to prevent typing extra zeroes, even if you are entering 0.000012. This trick works for entering numbers in thousands, millions, thousandths, millionths or any order of magnitude.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1817: Quickly Enter Millionths.
Hey.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
I was talking to Jim from Timken the other day, talking about our favorite Excel tricks, and Jim has a job where they are measuring tolerances and the numbers that he has to enter are in millions, like this, so, 12-millionth, .000012, which is a horribly frustrating thing to do.
If you would miscount the zeros and just go .00012, you're off by a factor of 10 and all the calculations are wrong.
So, rather than having to do .00012 and trying count all that, Jim found this great hidden feature under File, Options, come back here to Advanced, and it is automatically insert a decimal point, and you get to control the number of places.
Now, I recognize this from the old, old days when we had an adding machine.
So, for example, you would enter 1234 and it would become $12.34, but Jim kicked this up to six decimal places.
Click OK and what we can do now is just simply enter 12.
Bam. 9, 16, 11, 19, 3, and 5, and Excel inserts the right number of decimal places every single time.
Now, it's interesting there in that little tool tip that we saw.
It's also possible, File, Options, Advanced.
They say that this can be a negative number.
So, if we had to enter numbers in, let's say, thousands or something like that, I can enter 12 and it would become 12,000.
So, it can be both positive -- which moves the number to the right of the decimal place -- or negative -- which moves numbers to the left of the decimal place.
Great, great feature that I always figured was just kind of mirroring the old adding machines but clearly here has lots of other uses for Jim entering tolerances in millionths or, for someone who has to enter data in thousands or millionths or billionths, prevent you from having to enter all of those rows.
Well, hey, I want to thank Jim for passing that tip along.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1817: Quickly Enter Millionths.
Hey.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
I was talking to Jim from Timken the other day, talking about our favorite Excel tricks, and Jim has a job where they are measuring tolerances and the numbers that he has to enter are in millions, like this, so, 12-millionth, .000012, which is a horribly frustrating thing to do.
If you would miscount the zeros and just go .00012, you're off by a factor of 10 and all the calculations are wrong.
So, rather than having to do .00012 and trying count all that, Jim found this great hidden feature under File, Options, come back here to Advanced, and it is automatically insert a decimal point, and you get to control the number of places.
Now, I recognize this from the old, old days when we had an adding machine.
So, for example, you would enter 1234 and it would become $12.34, but Jim kicked this up to six decimal places.
Click OK and what we can do now is just simply enter 12.
Bam. 9, 16, 11, 19, 3, and 5, and Excel inserts the right number of decimal places every single time.
Now, it's interesting there in that little tool tip that we saw.
It's also possible, File, Options, Advanced.
They say that this can be a negative number.
So, if we had to enter numbers in, let's say, thousands or something like that, I can enter 12 and it would become 12,000.
So, it can be both positive -- which moves the number to the right of the decimal place -- or negative -- which moves numbers to the left of the decimal place.
Great, great feature that I always figured was just kind of mirroring the old adding machines but clearly here has lots of other uses for Jim entering tolerances in millionths or, for someone who has to enter data in thousands or millionths or billionths, prevent you from having to enter all of those rows.
Well, hey, I want to thank Jim for passing that tip along.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
