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.
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.
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.
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.
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.
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!
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.
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.
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!
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! 🙂
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
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