# New Excel Feature: Automatic Data Conversion for Numbers

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

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.

## 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.

• vimpha says:

#automaticdataconversions

• Mia Batson says:

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.

• Trevor Duguid Farrant says:

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.

• Jon Acampora says:

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

• Nauthstar says:

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.

• Graham says:

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.

• SHowell says:

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!

• Mike says:

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!

• Jon Acampora says:

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

• Anne-Mie Vanhulle says:

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.

• Jon Acampora says:

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

• Kirt says:

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!

• Jeff says:

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.

• Lourdes Manguerra says:

Amazing feature! The disappearing leading zeros have been my headache for a long time

• Sam M Cohen says:

I’d like to keep leading zeros and keep them as numbers

• Lisa says:

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!

• Jon Acampora says:

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.

• Cindy says:

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.

• khalid says:

thank you, this is a very useful tutorial , I have learnt many too much new from this

Generic filters
Exact matches only

#### Excel Shortcuts List

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List