Bottom Line: Learn about new data conversion settings that will prevent errors with leading zeros, long numbers, and scientific notation.
Skill Level: Beginner
Watch the Tutorial
Download the Excel File
The file I use in the tutorial can be downloaded by clicking below.
A New Feature for Excel
Have you ever had someone try to “help” you and they only made things worse? (If you've ever had toddlers, you know!) Sometimes Excel wants to “help” you by automatically converting numbers into another form, but it's not actually what you want.
Microsoft calls this Automatic Data Conversion, and they are testing out some new setting options that allow you to turn that off for three types of entries: numbers with leading zeros, long numbers, and scientific notation.
These options are currently in Beta testing, but when the general populace gets access to them, you'll find them under the Advanced section of the Excel Options window under the Automatic Data Conversion header.
Let's take a look at the three situations where we can now turn off Automatic Data Conversion.
Leading Zeros
The first option for turning off automatic data conversion is for situations where there are leading zeros in your number. By default, Excel will automatically remove any extra zeros before a number. But there may be circumstances when you want the zeros to show, such as with these New York zip codes.
To turn off the automatic removal of leading zeros, simply uncheck that option on the Excel Options window.
Long Numbers
When you have a number that's over 15 digits, Excel will automatically round the number and convert it to scientific notation. By unchecking the box on the Excel options window, you can turn off that automation.
Scientific Notation
The last scenario applies when you've entered data that has the letter E placed toward the end in a string of digits. Excel thinks you are trying to indicate a scientific notation and it converts it to appear that way. Sometimes, however, you may not want that automatic conversion. (For example, you may just be typing a model number that happens to have an E toward the end.) To turn off that automation, you can uncheck the box for that on the Excel Options window.
One thing to note is that the scientific notation formatting takes precedent over the conversion setting, so if it's already in place when you change the settings you will have to go back and adjust the cell formatting for the cells in question.
Current Ways of Preventing Data Conversion
Because you likely don't have these Beta features yet, you may be wondering if there's a current workaround for Automatic Data Conversion. Currently, to stop Excel from automatically converting a number to a format you don't want, you need to change the format from number to text. Data that is entered in text format will appear exactly how you type it. There are two ways to change the format from number to text.
1. Change Cell Formatting
With your desired range of cells selected, go to the Home tab. In the Number section, you can select Text in the dropdown menu of formats.
The trick is to convert the formatting BEFORE entering the value.
2. Use an Apostrophe
The other option is to simply type a single apostrophe (') before your entry. This tells Excel to store the data as text. The apostrophe that you type will not be displayed in the cell, but you can see it in the formula bar.
You'll notice the little green triangle in the corner of the cell alerting you that the value you entered is being stored as text.
Opening .CSV Files
These options that we've talked about also apply when you open .CSV files. However, there is also an option that you can check so that you get a warning about data conversion when you open .CSV files. This is a helpful feature.
See the video above for a demonstration of how Excel will prompt you with the warning message when opening a .CSV file.
Availability
The Automatic Data Conversion features are currently available for Microsoft 365 subscribers on the Insiders Beta Channel of Excel for Windows (desktop). The feature is flighted, so you might not have it yet even if you are on the beta channel.
The Office Insiders program is free to join. Here is Microsoft's blog post announcing the new features. You can stay up to date with changes and new features on their help page for Automatic Data Conversions.
Microsoft would like to gather feedback before fully releasing the feature to other channels and end points.
Conclusion
You can leave your feedback about these new options in a comment below and I will pass your feedback directly to the Excel team at Microsoft. Or, you can leave feedback directly through the Microsoft portal by going to Feedback on the Help menu in Excel. Be sure to use the hashtag #automaticdataconversions.
This just made my afternoon!
#automaticdataconversions
Thank you Microsoft for this option. I am often fighting the automatic conversion with my data and will spend time retyping numbers with an apostrophe.
Hello Jon, very interesting, as indeed are most of the tips & tricks you share. This new feature might help with the simple case where the Excel sort algorithm does not sort in the same way a human would. I’ve created an example which I’ll send to you. Who knows, it might be good enough to appear in a future video.
Hey Trevor,
Great point! A column with text and numbers will not sort the way you expect it to. That does sound like a good topic for a future post.
Thanks again and have a nice weekend! 🙂
I only see TWO problems with these new features.
1. It takes several years for them to be rolled out to every version of Excel (Corporate Excel, Office 365 Home user etc. etc.)
2. That will cause lots of issues for people using/sharing files between each other (on different versions) especially when creating macros &/or formulas that use the stored data.
Hi John
Thanks for this interesting article. I look forward to seeing & using these new features/options when they spread to the iOS Excel.
An irritating example is the conversion by Excel of date-time strings like 202207081234 to scientific notation, 2.0221E+11. Then when that’s converted to a number it acquires .00 on the end, which requires removal.
A similar but different automatic conversion that I find frustrating is Excel’s conversion of certain strings into dates. That appears to be impossible to undo. Examples: 5/7 (converted to 05-Jul for me in the UK), 2-4 (converted to 2 April). An initial apostrophe again helps.
Yet another is the way Excel changes a date-time value like 2022-07-28 07:45:00.497000 UTC to 2022-07-28T07:45:00.497Z, which would be fine, but for the insertion of a T that makes it almost unreadable.
This would have been handy last year! I had to upload 6000 vendor addresses/EIN’s up to Dynamics 365 F&O. Many of our zip codes/EIN’s had leading zeros. Data management will only deal with General format: no apostrophe’s, no text format on typical “number only” entries. A lot of time cleaning up the data!
Thanks Jon. I often want to convert a date to a text (eg for charts) but a text that looks like the date I converted [eg 28 July 22 should look like 28 Jul 22 _ I am also UK based]. If I just change to text if comes out 28/07/22. I can put in an apostrophe but that is tedious for several lines. Is that option possible at he moment – if not I hope that it can be added!
Hey Mike,
Great question! Date conversions are not part of this update yet. On Microsoft’s blog post about this feature, it mentions that “Support for automatic conversion to dates is planned but not yet available.”
So hopefully that will be coming in the future.
Thanks again and have a nice weekend! 🙂
You actually can DO (as opposed to it just happening) that conversion using an input which is an actual date number (formatted however it is formatted, so with it displaying “28 July 22” or “7/28” (with 2022 being the undisplayed year, of course) it will still work just fine) or a text version like “28 July 22” by entering the first output and using the fill handle to let Excel give you the rest.
Then do as appropriate for you (keep the helper column, or copy it and paste over the original data, or some variation in between, or even, if the helper column is a column on a general helper sheet, you might refer to that place in formulas depending on it) regarding how you use that output.
Not the same as typing it in or importing and Excel automatically changing it as it populates, but closer than typing it or building a conversion formula. (Of course, a custom format isn’t tough, but not everyone likes those and it might be a text input which wouldn’t take it anyway.)
Well…
MY BAD.
I used a limited data set for the fill handle test. Turns out it is just incrementing the day of the month value and happily gives “32 Jul 22_” for any date after 31 Jul…
And that happens whether the input is text or numerical.
Something I have seen lots before, but didn’t think about when testing. Senility… it’s not just what you do looking at that river in Egypt anymore.
The fact that the leading zero’s stay is for me the most important feature. Especially when importing CSV files. I allready created a macro that imports everything as text. This feature will make my macro obsolete.
Another way to show leading zeros is by adapting the format to “000000”
I also created a macro to convert a selected number of cells to values. That way I can quickly convert from one format to another
What I find anoying however is that it is showing as an error.
Thanks for suggesting the additional technique to show leading zeros.
I agree that the error being displayed is a bit weird. That will be good feedback for Microsoft as they might be able to hide it when the conversion setting is turned off. I’m not saying it’s going to happen, but hopefully could be an option.
Thanks again and have a nice weekend! 🙂
As someone who deals with leading zeros and the problems they cause on a daily basis, they cannot install this option soon enough. I don’t use scientific notations, but I do have issues with these when updating UPC codes. It’s usually an easy fix (number format and get rid of the right two zeros) but not dealing that issue at all would be great. Thank you!
We have a lot of legacy product part numbers with leading zeros that are always being wiped out when we import the information into excel. This would be huge in our company.
Amazing feature! The disappearing leading zeros have been my headache for a long time
I’d like to keep leading zeros and keep them as numbers
FINALLY!
I’ll be able to use long numbers in excel and be able to deduplicate and use them with vlookup! I’ve been waiting about 8 years for this feature! I CAN’T WAIT until it’s available!
I work for a very large grocery retailer that uses 18 digit numbers to identify specific customer data. Right now I have to import the list of numbers to another database in order to manipulate them down to 17 digits, deduplicate, and match them with lists from other data sources. What a waste of time!
Thanks for sharing your scenario, Lisa! That does sound like a lot of extra time and I’m sure Microsoft will be happy to hear that the feature will save you time and make things easier.
This sounds like a handy feature overall. But similar to the sorting issue, another drawback I can think of is if you are using a v- or xlookup on that data, and one source of data is all numbers or (more likely) all text, but your converted data has some of each. Looking up a text string will not find a numeric match and vice versa. I’d almost rather have it convert the whole data column to text, even data without leading zeroes, since you typically do not have to perform numeric calculations on data such as zip codes, part numbers, SSNs, etc.
thank you, this is a very useful tutorial , I have learnt many too much new from this
Parts I like best are the opportunity to tell Excel to leave my CSV files alone, just display them and let me work with them as CSV files, not Excel files, and the long numbers being able to be entered as is and left alone.
For the latter, it’s always seemed almost criminal that a program ostensibly aimed at financial and business work makes it impossible to enter a credit card number with great effort and precarious footing.
Some years back, the leading 0 part would’ve been very attractive as well, but I don’t run into much issue with that issue with my current work. But I feel the ya’s that love that!
An oddity, found experimenting with whether Excel would treat these text long digits strings as numbers when context makes it clear that is desired. (So in general, say you have a “2” formatted as text. You can lookup that data presenting it to your lookup function as text and find it, or as a numeral and not find it. That’s because you might easily want to look up exactly what you show and not want “hits” on others that seem the same… Excel doesn’t know so it enforces being exact. But that same data, text that it is… if you write a formula to add perhaps 7 to it, Excel sees no uncertainty in what you desire and does the arithmetic giving you 9. So no need for contortions to check if the data is numerical or text and changing it to a value before finally adding 7 to it. As the legend says: “Velly nice, velly nice!” That situation is what I’m looking at here.)
So, the oddity. I entered a 19 digit number and saw it laid out as text. Then added 2 to it. I’m sure that in the past, Excel would’ve truncated it at 15 digits, then added 2 to that value. But in this case, Excel apparently added the 2 to the 19th digit which, as it was the 9 in an ending of 99, should have kicked it to a 20 digit number, but instead did “truncate” in that it kept the first 15 digits, but then it added four 0’s to the end of it, giving a total of 19 digits, matching the original.
Seems to me the explanation of its process was:
1. See that I desired an addition.
2. Did the addition using the text string plus the 2 and failed. Instead of giving the #VALUE! error, it saw it needed to do more before rendering a result so…
3. It decided to keep the text string, not the 2, and then…
4. As it still was trying to produce a numerical output, as clearly was my desire, it truncated that text string to 15 numerical character and made them numerical, not text.
5. And then added the four 0’s to keep the original’s length.
6. Finallly, it displayed as a number, in all ways showing the extra 0’s for 19 digits, not 15, and ended its work.
(Order of entry in the formula did not matter: “=G3+2” and “=2+G3” produce the same result.)
Something to keep in mind for situations in which one expects the errors and might think none of the data has such strings as no errors occur. And for times whn one might use an ISNUMBER test on the result (TRUE on the result here, FALSE on the numbery text input I was adding 2 to.)