Atlanta Power Excel Seminar:
Is there a way to have Excel always shift my numeric entry so the last 2 digits appear after the decimal place?
Yes - File, Options, Advanced. Automatically insert a decimal point.
Choose that setting and specify the number of digits
To convert all entries to thousands, change the number of digits to -3
To download this workbook: https://www.mrexcel.com/download-center/2018/05/insert-2-decimals.xlsx
Is there a way to have Excel always shift my numeric entry so the last 2 digits appear after the decimal place?
Yes - File, Options, Advanced. Automatically insert a decimal point.
Choose that setting and specify the number of digits
To convert all entries to thousands, change the number of digits to -3
To download this workbook: https://www.mrexcel.com/download-center/2018/05/insert-2-decimals.xlsx
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2207: Insert 2 Decimals.
I got this question in Atlanta, and I love this question because I remember the very first time-- I was probably six or seven-- my parents went over to their friends' house, and their friends owned the local gas station in town, right?
And so, in the home office in their house was this amazing machine-- the first time I'd ever seen anything like it.
It was an old adding machine, right?
With the tape?
And I remember there was a switch there, this amazing switch, that when you clicked that switch, it would automatically insert a decimal point.
So, if you typed 12345, it would enter 123.45.
Alright?
And so I was in Atlanta, and someone asks me this question, and says, "Hey, how can we do that?
How can we type 12345 and have it change to 123.45?" And I knew there was a setting in Excel to do this.
It's under Excel Options; Advanced; second choice: "Automatically insert a decimal point." So, File; Options; go to Advanced-- Advanced is, usually, you have to scroll all the way down… there's a hundred miles of stuff here; I'm just going to "Automatically insert a decimal point"; leave it at 2 places; click OK; and then I type 12345, and BAM! -- it's 123.45.
I ran into a guy up at the VFW in Ohio who used this for a different thing.
[ File; Options... ] He was always entering something with 6 decimal places-- some sort of measurement.
So, 4, 5, 6-- like this-- and he would type 123456, and it would become a decimal.
Let's increase the number of decimal places there, so we can see it.
And then if it was a measurement, like where it was not all the digits, 1234, it would insert extra spaces.
Alright, so, really, really cool thing.
Now, the question that came up in Atlanta, though, is, "Can we make this happen in just one range?" And, no.
This is a global thing.
Once you change that setting, it's going to happen in all cells, all sheets, until you go change it back.
So I guess the answer is: Just use it while you have to key a bunch of numbers in, and then, when you're done keying numbers, then you come in and turn it off, like that.
Still-- a cool, cool way to go.
Well, hey, check out my new book, MrExcel LIVe, The 54 Greatest Tips of All Time.
Click that "I" in the top right-hand corner for more information.
Woah-- hang on a second-- hold the phone-- did you see that tooltip?
File; Options; Advanced; and, when I happened to hover there, they said, "Choose a number between -300 and 300." Which seems to indicate that we can "Automatically insert a decimal point," and if we go to -3, that will mean that I can enter numbers-- if it's 123,000, I can just enter 123, and they should enter it as 123,000-- like that, how cool is that?
Alright, it goes both ways, just like the ROUND function does.
You can specify 2 for 2 digits after the decimal place, or -2 to round to the nearest 100.
So, even better like that.
Alright, question from the Atlanta Power Excel Seminar-- Is there a way to have Excel always shift my numeric entries so the last few digits appear after the decimal place?
Yes.
It's File; Options; Advanced; second choice-- "Automatically insert a decimal point;" choose that setting and specify the number of digits.
To convert all entries to the 1000s, you can put a negative number of digits-- so 123 becomes 123,000.
Put 2 digits and 12345 becomes 123.45.
One hassle-- you can't do this to just a range.
It's global.
It's going to work in all cells, all sheets, all workbooks.
So turn it on when you need to do some data entry, and then turn it off.
To download the workbook from today's video, in case you want to work along, visit the URL in the YouTube description.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I got this question in Atlanta, and I love this question because I remember the very first time-- I was probably six or seven-- my parents went over to their friends' house, and their friends owned the local gas station in town, right?
And so, in the home office in their house was this amazing machine-- the first time I'd ever seen anything like it.
It was an old adding machine, right?
With the tape?
And I remember there was a switch there, this amazing switch, that when you clicked that switch, it would automatically insert a decimal point.
So, if you typed 12345, it would enter 123.45.
Alright?
And so I was in Atlanta, and someone asks me this question, and says, "Hey, how can we do that?
How can we type 12345 and have it change to 123.45?" And I knew there was a setting in Excel to do this.
It's under Excel Options; Advanced; second choice: "Automatically insert a decimal point." So, File; Options; go to Advanced-- Advanced is, usually, you have to scroll all the way down… there's a hundred miles of stuff here; I'm just going to "Automatically insert a decimal point"; leave it at 2 places; click OK; and then I type 12345, and BAM! -- it's 123.45.
I ran into a guy up at the VFW in Ohio who used this for a different thing.
[ File; Options... ] He was always entering something with 6 decimal places-- some sort of measurement.
So, 4, 5, 6-- like this-- and he would type 123456, and it would become a decimal.
Let's increase the number of decimal places there, so we can see it.
And then if it was a measurement, like where it was not all the digits, 1234, it would insert extra spaces.
Alright, so, really, really cool thing.
Now, the question that came up in Atlanta, though, is, "Can we make this happen in just one range?" And, no.
This is a global thing.
Once you change that setting, it's going to happen in all cells, all sheets, until you go change it back.
So I guess the answer is: Just use it while you have to key a bunch of numbers in, and then, when you're done keying numbers, then you come in and turn it off, like that.
Still-- a cool, cool way to go.
Well, hey, check out my new book, MrExcel LIVe, The 54 Greatest Tips of All Time.
Click that "I" in the top right-hand corner for more information.
Woah-- hang on a second-- hold the phone-- did you see that tooltip?
File; Options; Advanced; and, when I happened to hover there, they said, "Choose a number between -300 and 300." Which seems to indicate that we can "Automatically insert a decimal point," and if we go to -3, that will mean that I can enter numbers-- if it's 123,000, I can just enter 123, and they should enter it as 123,000-- like that, how cool is that?
Alright, it goes both ways, just like the ROUND function does.
You can specify 2 for 2 digits after the decimal place, or -2 to round to the nearest 100.
So, even better like that.
Alright, question from the Atlanta Power Excel Seminar-- Is there a way to have Excel always shift my numeric entries so the last few digits appear after the decimal place?
Yes.
It's File; Options; Advanced; second choice-- "Automatically insert a decimal point;" choose that setting and specify the number of digits.
To convert all entries to the 1000s, you can put a negative number of digits-- so 123 becomes 123,000.
Put 2 digits and 12345 becomes 123.45.
One hassle-- you can't do this to just a range.
It's global.
It's going to work in all cells, all sheets, all workbooks.
So turn it on when you need to do some data entry, and then turn it off.
To download the workbook from today's video, in case you want to work along, visit the URL in the YouTube description.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
