Shanna sends in today's question. When she types three digits in a cell such as 150, the cell automatically changes to 1.5. Episode 997 takes a look at this bizarre problem and when this setting might actually be useful.
This video is the 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!
This video is the 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!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Basically, we start out with massive amount of data.
We say how we're going to analyze this.
Well, let's fire up a pivot table.
Let’s see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today's problem is sent in by Shanna and this is one of those problems that when you finally discover the solution and say wait a second, there's probably a cool use for this.
She has said she had a spreadsheet and she goes and she types a Hundred and Fifty, 150 and presses Enter.
And look at that it changed to 1.5.
Now, when she initially said this I thought that maybe someone had formatted the cell previously, as a percentage.
Showing you one hundred fifty percent.
And then you can come in here and enter 175 and it really enters 1.75 in the cell.
So, I initially thought oh, no it's a percentage problem.
And I asked you to check the format.
She came back and she said no, you know that's not working at all because if we change the format...
And I said I want to see it with zero decimal places.
It's changing to two and two decimal places I get 1.50.
And you know I was like that's bizarre.
Here's what's happened is there's a setting in Excel options that affects just this particular worksheet.
If we go back to the Advanced Tab Editing options, automatically insert a decimal point 2 places.
Alright! So, somehow that had gotten checked on Shannon's computer.
And so what happens there is if you have a lot of data to enter and you don't want to have to enter the decimal point.
So, 123456, 5,6 being the decimal.
When I press Enter, it automatically inserts the decimal point.
Prevents you from having to go over and hit the decimal point all the time.
Kind of a cool feature if you had a lot of data entry to do.
Now, in this particular case, it's somehow it got turned on accidentally And you know that was a bad thing because now every time they type 250 they were getting 1.5.
Actually, would have to type 150 00 to get 150.
And very frustrating.
Obviously, once Shanna discovered that setting under Excel Options, things were much much better.
So, there you go something to tuck away.
In case, it ever happens to you or more likely if you need to do some data entry, don't want to have to enter the decimal points all the time.
Turn on that great format.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Basically, we start out with massive amount of data.
We say how we're going to analyze this.
Well, let's fire up a pivot table.
Let’s see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today's problem is sent in by Shanna and this is one of those problems that when you finally discover the solution and say wait a second, there's probably a cool use for this.
She has said she had a spreadsheet and she goes and she types a Hundred and Fifty, 150 and presses Enter.
And look at that it changed to 1.5.
Now, when she initially said this I thought that maybe someone had formatted the cell previously, as a percentage.
Showing you one hundred fifty percent.
And then you can come in here and enter 175 and it really enters 1.75 in the cell.
So, I initially thought oh, no it's a percentage problem.
And I asked you to check the format.
She came back and she said no, you know that's not working at all because if we change the format...
And I said I want to see it with zero decimal places.
It's changing to two and two decimal places I get 1.50.
And you know I was like that's bizarre.
Here's what's happened is there's a setting in Excel options that affects just this particular worksheet.
If we go back to the Advanced Tab Editing options, automatically insert a decimal point 2 places.
Alright! So, somehow that had gotten checked on Shannon's computer.
And so what happens there is if you have a lot of data to enter and you don't want to have to enter the decimal point.
So, 123456, 5,6 being the decimal.
When I press Enter, it automatically inserts the decimal point.
Prevents you from having to go over and hit the decimal point all the time.
Kind of a cool feature if you had a lot of data entry to do.
Now, in this particular case, it's somehow it got turned on accidentally And you know that was a bad thing because now every time they type 250 they were getting 1.5.
Actually, would have to type 150 00 to get 150.
And very frustrating.
Obviously, once Shanna discovered that setting under Excel Options, things were much much better.
So, there you go something to tuck away.
In case, it ever happens to you or more likely if you need to do some data entry, don't want to have to enter the decimal points all the time.
Turn on that great format.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
