(Originally published on July 5, 2022 by Chirag Fifadra)
Hi, Insiders. My name is Chirag Fifadra, and I'm a Product Manager on the Excel team. I'm excited to share with you an improvement to Excel for Windows that will give you more control over data conversions. More specifically, you can now control how and whether the data you enter is automatically converted to specific formats.
Control data conversions
We have consistently heard from customers over the years (and likely from some of you!) that they're frustrated by the fact that Excel automatically converts data to specific formats. They have expressed a desire for more control over data conversions.
We are now giving you the ability to change Excel’s default behavior and disable specific types of automatic data conversions as needed. This way, you won’t need to worry about your data being converted to a format that you didn't want and weren't expecting.
How it works
To disable specific automatic data conversions, select File > Options > Advanced > Automatic Data Conversion. Then choose the conversion(s) that you'd like to disable.
Currently, you can enable or disable the following options:
- Remove leading zeros from numerical text and convert to a number.
- Truncate numerical data to 15 digits of precision and convert to a number that may be displayed in scientific notation, if needed.
- Convert numerical data surrounding the letter "E" to a number displayed in scientific notation.
NOTE: You can also select the When loading a .csv file or similar file, notify me of any automatic number conversions check box. By doing so, Excel will display a warning message when it detects that at least one of the optional automatic data conversions is enabled and about to occur when opening a .csv or .txt file. The message gives the ability to do a "one-time load" of the file without converting the data.
Scenarios to try
Based on the settings you chose above, try some or all of the scenarios below to test the increased control over data conversions.
- Type directly into a cell.
- Copy and paste from external sources (e.g., a web page).
- Open a .csv or .txt file.
- Find and replace operations.
- Select Data > Text to Columns, and then use the Convert Text to Columns Wizard.
NOTE: Since the feature works by saving the entered data as text, you might see a green triangle with a Number stored as text error. This is expected. You can dismiss the error by selecting Ignore error in the context menu. Also, you may not be able to use that data in mathematical operations.
Known issues
- Support for disabling these conversions during macro execution is planned but not yet available.
- Support for automatic conversion to dates is planned but not yet available.
Availability
This feature is available to Office Insiders running Version 2207 (Build 15427.20000) or later on Windows. We are working to bring the same options to Mac soon.
Features are enabled gradually over a period of time so that we can ensure things are working smoothly. Even though we've announced this feature, it might not yet be available to you. Sometimes we remove elements to further improve them, based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as an Insider, have had the opportunity to try it.
Feedback
We want to hear from you! Please click Help > Feedback, in Excel and select one of the Feedback options. Enter #automaticdataconversions in the text box along with your comments, and then click the Submit button.
Learn about the Microsoft 365 Insider program and sign up for the Microsoft 365 Insider newsletter to get the latest information about Insider features in your inbox once a month!