I love this feature so much, I turned it off!
For decades, Excel has been performing default data conversions:
Remove leading zeroes
Keep first 15 digits of long numbers and convert to scientific notation
Convert digits surrounding an E to scientific notation.
New in Insiders beta, Excel will ask if you want to do all three of those things. The first time, I was happy to click Don't Convert.
But I think the real solution is to embrace all three options and turn all three of them off! They have been annoying us for so long. Why would you want Microsoft to ask, "Hey, should we keep annoying you?". For me, I am turning all three of these off.
Table of Contents
(0:00) I love this so much, I am turning it off
(1:10) Numbers longer than 15 digits
(1:59) Demo of new box
(2:15) Column not wide enough
(2:51) If you Convert
(3:12) Previous default no warning
(3:45) Remove leading zeroes
(4:14) Custom number format ask?
(4:50) Digits surrounding E
(5:30) Do all 3 or not
(5:45) Don't Notify checkbox
(6:05) No ask if pasting
(7:13) SUM ignores text
(7:40) Typing data
(9:05) What settings will I use
(10:57) Guerrilla Data Book
(11:28) Nancy Faust
(11:34) Subscribe
(11:42) Retrieve course
For decades, Excel has been performing default data conversions:
Remove leading zeroes
Keep first 15 digits of long numbers and convert to scientific notation
Convert digits surrounding an E to scientific notation.
New in Insiders beta, Excel will ask if you want to do all three of those things. The first time, I was happy to click Don't Convert.
But I think the real solution is to embrace all three options and turn all three of them off! They have been annoying us for so long. Why would you want Microsoft to ask, "Hey, should we keep annoying you?". For me, I am turning all three of these off.
Table of Contents
(0:00) I love this so much, I am turning it off
(1:10) Numbers longer than 15 digits
(1:59) Demo of new box
(2:15) Column not wide enough
(2:51) If you Convert
(3:12) Previous default no warning
(3:45) Remove leading zeroes
(4:14) Custom number format ask?
(4:50) Digits surrounding E
(5:30) Do all 3 or not
(5:45) Don't Notify checkbox
(6:05) No ask if pasting
(7:13) SUM ignores text
(7:40) Typing data
(9:05) What settings will I use
(10:57) Guerrilla Data Book
(11:28) Nancy Faust
(11:34) Subscribe
(11:42) Retrieve course
Transcript of the video:
There are so many times when we get a new feature from Microsoft and I instantly turn that feature off.
But today this is a feature that I love.
And I think you should go in and unchoose the new boxes to embrace this amazing new feature.
There's a lot to unpack in a very simple sentence.
This new feature handles when you're loading data from CSV or Text, pasting from another app, or typing data.
If you're doing any of those actions that involve any of these three things.
Numbers with leading zeros.
Numbers with more than 15 digits, for example, credit card numbers, or just any number that has more than 15 digits.
Or any number that happens to have an E in it, by default, Excel has been converting those with no warning in the past. Now you're going to see a warning.
The question is whether we want to leave that warning there or not.
So let's take a look at the three things that they have always been fixing.
All right, first one, keep the first 15 digits of long numbers and display in scientific notation.
So I have this transaction number here, 17 digits.
I had to count, 5, 10, 15, 17, almost always alphanumeric.
But in a large dataset that I recently imported, I happened to just random dumb luck get one that had no alpha and it was all numeric.
When I went to open the CSV file, this new box pops up that says, by default, Excel will convert those large numbers into scientific notation.
"Do you want to permanently keep these conversions?" You have a choice of convert, which is the default, or don't convert.
Well, hang on.
Let's just do this, because it threw me for a loop when I first did it. It was confusing.
It takes me through the Text Import Wizard. I'm just going to click finish.
Here they say they're going to convert to scientific notation.
What catches my eye is column D. That's all in scientific notation.
So I say don't convert and I'm super annoyed that everything's in scientific notation, but it's not.
157200082 is in general. It's just not wide enough.
So Excel isn't making the column wide enough. That wasn't the problem at all.
The problem, as I discovered later, is over here in the transaction column, almost all of these are alphanumeric.
That one, which happened to be all numeric, now comes in with a little green triangle, meaning that it's text.
Let's contrast that if I would've said convert.
Finish, convert. Okay.
There's that number which now ends in 00. A few seconds ago, it did not end in 00.
This is an absolutely evil thing to have happened.
It was always happening by default with no warning in the past.
So the fact that they even ask me this question is a huge improvement.
But a bigger question is when would I ever want to lose precision? I'm not dealing with numbers this big.
This one just happens to be a transaction ID.
So this is one that I'm really glad that they're warning me about it and giving me a choice whether to convert or not convert.
So I think in this case, convert large numbers into scientific notation.
For me, the answer will always be don't convert so we'll actually get the entire transaction number.
All right, next up, remove leading zeros and convert to number.
So, of course, these are all numbers here, 000210747.
If you would say convert, then you get the number without the leading zeros.
If you would say don't convert, then it actually turns those cells into text.
We get the green triangle, they're left-aligned, and the leading zeros stay there.
I suppose it would've been asking way too much to have a fourth button here that would do what I would like it to do, which is use a custom number format with the right number of zeros to make it still numeric, but to look correct.
For once, I don't want to sound like a whiner, and so I'm not going to ask for that.
Just understand your choices are lose the leading zeros or convert to text. Neither one of these is ideal.
Third option convert digits surrounding the letter E to a number in scientific notation.
I've done a lot of seminars over the years, 17 years times 35 seminars, and there was one fellow that I ran into who was super annoyed with this problem, because they had a top-selling product where the second to the last digit was an E.
Right here, there was a number where the second to the last digit is an E. It was E9.
He was trying to convert it into scientific notation.
Again, I'm almost always going to say don't convert.
So that number comes in and the E stays there.
All right. So far, this is great.
Three issues, and Excel has given us a choice in those issues.
Let's just be aware of something here.
When you first encounter this, in this particular case, I had a file that had all three, and my choices are either do all three or don't do all three.
Be aware of this, "Don't notify me about default conversions".
If you just choose that, it's going to turn this off and keep all three of these turned on. I don't think that's what anyone really wants.
This seems like a sort of dangerous box.
And don't get too comfortable in the fact that they ask when you're opening CSV files and Text files, because this option impacts three things, loading data from CSV or Text, pasting from another app, or typing data. All right, so let's go in.
File, options, advanced. Scroll down a little bit.
All three of these are turned on. Click okay.
Here's the text file with all three problems. I'm going to copy and paste.
We've lost precision here. Those digits are gone.
It converted this one to scientific notation and we lost our leading zeros there.
Now I'm going to go into options. File, options, advanced, and turn everything off.
Click okay and do the exact same paste. You see now we get our final numbers there.
That E remains in the part number and we have the leading zeros, although it's now text instead of numeric. That's only a problem if we need to sum that data.
The sum will be wrong because the 278 won't be included.
Let's go back.
File, options, advanced. I like that it's near the top.
We can get there quickly. I'll turn everything back on.
Now I'm just typing 00054, changes to 54. 123E4 changes to scientific notation.
123E4. Yup, that's right.
1.23 E to the Sixth, or a really long number, 1234567890123456789 will get converted to scientific notation and we'll lose the precision at the end.
I think what's really new here is the ability to turn this off.
File, options, advanced.
Enable all default data conversions, uncheck that. Now when we type 00054, the leading zero stay.
123E4, if that's a part number, that stays. And 1234567890123456789 stays as a long number.
I'm pretty sure that I prefer all of these to these.
I mean why would I type 00054 if I didn't want the zeros to appear there?
Maybe if I'm a scientist or an engineer, I would want the ability to type numbers in scientific notation. So what settings am I going to use?
As I gave careful consideration to all three of these, I decided to uncheck them all.
Then once they're all unchecked, I unchecked don't bother to tell me if you're about to do any conversions because you're not.
I've prevented you from doing it.
There's a lot of new features that come along and I immediately turn it off because I don't like the feature.
This new feature that comes along is something completely different, though.
I'm going to turn them all off because I love the features.
These are things that have been annoying me for decades, and the ability to turn these off, I think is a really good thing.
I can understand why they didn't turn them off by default, because it's changing some long-held behaviors, long-held behaviors that have been annoying, I'm going to say, 94% of us.
Now if you're in that 6% who's counting on the E to really mean scientific notation, if you're loading that data in, or if you're typing 123E17, then maybe you leave that one on and choose whether or not to get the warning.
But I really think ...
Boy, I hope this doesn't come back to bite me someday.
I really think I'm going to leave all of these off and just be happy that I no longer am losing precision on long numbers.
The things that happen to have an E, or surrounded by digits, won't be scientific notation anymore and the leading zeros will stay.
I think that's it. I think that's it.
So kudos to Microsoft.
Again, it's only in Insiders Fast, which is the beta channel, but it'll roll out. So watch for this to show up.
I think it's a great thing. Hey, check out this brand new book.
Guerrilla Data Analysis Using Microsoft Excel was the first book that I wrote way back in 2002.
In 2013, Oz du Soleil updated that book for the second edition.
Now here in 2022, Oz and I have updated it for a third edition.
So much has been added since 2013, of course Power Query and all kinds of other things like that.
So Guerilla Data Analysis Using Microsoft Excel, third edition.
Click that 'i' on the top right hand corner for more information about that.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, like, subscribe, and ring the bell.
Feel free to post any questions or comments down in the comments below.
Now if you love Excel, check out my new courses on the Retrieve platform.
They're video courses.
But you just type what you're looking for, it takes you right to that spot in the video.
There's a complete transcript in several languages. It's a super fast way to learn.
But today this is a feature that I love.
And I think you should go in and unchoose the new boxes to embrace this amazing new feature.
There's a lot to unpack in a very simple sentence.
This new feature handles when you're loading data from CSV or Text, pasting from another app, or typing data.
If you're doing any of those actions that involve any of these three things.
Numbers with leading zeros.
Numbers with more than 15 digits, for example, credit card numbers, or just any number that has more than 15 digits.
Or any number that happens to have an E in it, by default, Excel has been converting those with no warning in the past. Now you're going to see a warning.
The question is whether we want to leave that warning there or not.
So let's take a look at the three things that they have always been fixing.
All right, first one, keep the first 15 digits of long numbers and display in scientific notation.
So I have this transaction number here, 17 digits.
I had to count, 5, 10, 15, 17, almost always alphanumeric.
But in a large dataset that I recently imported, I happened to just random dumb luck get one that had no alpha and it was all numeric.
When I went to open the CSV file, this new box pops up that says, by default, Excel will convert those large numbers into scientific notation.
"Do you want to permanently keep these conversions?" You have a choice of convert, which is the default, or don't convert.
Well, hang on.
Let's just do this, because it threw me for a loop when I first did it. It was confusing.
It takes me through the Text Import Wizard. I'm just going to click finish.
Here they say they're going to convert to scientific notation.
What catches my eye is column D. That's all in scientific notation.
So I say don't convert and I'm super annoyed that everything's in scientific notation, but it's not.
157200082 is in general. It's just not wide enough.
So Excel isn't making the column wide enough. That wasn't the problem at all.
The problem, as I discovered later, is over here in the transaction column, almost all of these are alphanumeric.
That one, which happened to be all numeric, now comes in with a little green triangle, meaning that it's text.
Let's contrast that if I would've said convert.
Finish, convert. Okay.
There's that number which now ends in 00. A few seconds ago, it did not end in 00.
This is an absolutely evil thing to have happened.
It was always happening by default with no warning in the past.
So the fact that they even ask me this question is a huge improvement.
But a bigger question is when would I ever want to lose precision? I'm not dealing with numbers this big.
This one just happens to be a transaction ID.
So this is one that I'm really glad that they're warning me about it and giving me a choice whether to convert or not convert.
So I think in this case, convert large numbers into scientific notation.
For me, the answer will always be don't convert so we'll actually get the entire transaction number.
All right, next up, remove leading zeros and convert to number.
So, of course, these are all numbers here, 000210747.
If you would say convert, then you get the number without the leading zeros.
If you would say don't convert, then it actually turns those cells into text.
We get the green triangle, they're left-aligned, and the leading zeros stay there.
I suppose it would've been asking way too much to have a fourth button here that would do what I would like it to do, which is use a custom number format with the right number of zeros to make it still numeric, but to look correct.
For once, I don't want to sound like a whiner, and so I'm not going to ask for that.
Just understand your choices are lose the leading zeros or convert to text. Neither one of these is ideal.
Third option convert digits surrounding the letter E to a number in scientific notation.
I've done a lot of seminars over the years, 17 years times 35 seminars, and there was one fellow that I ran into who was super annoyed with this problem, because they had a top-selling product where the second to the last digit was an E.
Right here, there was a number where the second to the last digit is an E. It was E9.
He was trying to convert it into scientific notation.
Again, I'm almost always going to say don't convert.
So that number comes in and the E stays there.
All right. So far, this is great.
Three issues, and Excel has given us a choice in those issues.
Let's just be aware of something here.
When you first encounter this, in this particular case, I had a file that had all three, and my choices are either do all three or don't do all three.
Be aware of this, "Don't notify me about default conversions".
If you just choose that, it's going to turn this off and keep all three of these turned on. I don't think that's what anyone really wants.
This seems like a sort of dangerous box.
And don't get too comfortable in the fact that they ask when you're opening CSV files and Text files, because this option impacts three things, loading data from CSV or Text, pasting from another app, or typing data. All right, so let's go in.
File, options, advanced. Scroll down a little bit.
All three of these are turned on. Click okay.
Here's the text file with all three problems. I'm going to copy and paste.
We've lost precision here. Those digits are gone.
It converted this one to scientific notation and we lost our leading zeros there.
Now I'm going to go into options. File, options, advanced, and turn everything off.
Click okay and do the exact same paste. You see now we get our final numbers there.
That E remains in the part number and we have the leading zeros, although it's now text instead of numeric. That's only a problem if we need to sum that data.
The sum will be wrong because the 278 won't be included.
Let's go back.
File, options, advanced. I like that it's near the top.
We can get there quickly. I'll turn everything back on.
Now I'm just typing 00054, changes to 54. 123E4 changes to scientific notation.
123E4. Yup, that's right.
1.23 E to the Sixth, or a really long number, 1234567890123456789 will get converted to scientific notation and we'll lose the precision at the end.
I think what's really new here is the ability to turn this off.
File, options, advanced.
Enable all default data conversions, uncheck that. Now when we type 00054, the leading zero stay.
123E4, if that's a part number, that stays. And 1234567890123456789 stays as a long number.
I'm pretty sure that I prefer all of these to these.
I mean why would I type 00054 if I didn't want the zeros to appear there?
Maybe if I'm a scientist or an engineer, I would want the ability to type numbers in scientific notation. So what settings am I going to use?
As I gave careful consideration to all three of these, I decided to uncheck them all.
Then once they're all unchecked, I unchecked don't bother to tell me if you're about to do any conversions because you're not.
I've prevented you from doing it.
There's a lot of new features that come along and I immediately turn it off because I don't like the feature.
This new feature that comes along is something completely different, though.
I'm going to turn them all off because I love the features.
These are things that have been annoying me for decades, and the ability to turn these off, I think is a really good thing.
I can understand why they didn't turn them off by default, because it's changing some long-held behaviors, long-held behaviors that have been annoying, I'm going to say, 94% of us.
Now if you're in that 6% who's counting on the E to really mean scientific notation, if you're loading that data in, or if you're typing 123E17, then maybe you leave that one on and choose whether or not to get the warning.
But I really think ...
Boy, I hope this doesn't come back to bite me someday.
I really think I'm going to leave all of these off and just be happy that I no longer am losing precision on long numbers.
The things that happen to have an E, or surrounded by digits, won't be scientific notation anymore and the leading zeros will stay.
I think that's it. I think that's it.
So kudos to Microsoft.
Again, it's only in Insiders Fast, which is the beta channel, but it'll roll out. So watch for this to show up.
I think it's a great thing. Hey, check out this brand new book.
Guerrilla Data Analysis Using Microsoft Excel was the first book that I wrote way back in 2002.
In 2013, Oz du Soleil updated that book for the second edition.
Now here in 2022, Oz and I have updated it for a third edition.
So much has been added since 2013, of course Power Query and all kinds of other things like that.
So Guerilla Data Analysis Using Microsoft Excel, third edition.
Click that 'i' on the top right hand corner for more information about that.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, like, subscribe, and ring the bell.
Feel free to post any questions or comments down in the comments below.
Now if you love Excel, check out my new courses on the Retrieve platform.
They're video courses.
But you just type what you're looking for, it takes you right to that spot in the video.
There's a complete transcript in several languages. It's a super fast way to learn.
