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.