Microsoft Finally Gives Users Control Over Excel’s Automatic Data Conversion

For all the Sage X3 data warriors, we’re spreading the word about Excel’s new feature that allows users to NOT drop leading zeros when opening files, NOT convert text to date, NOT convert to scientific notation, etc… This is long overdue and super useful.   

WHAT DID MICROSOFT CHANGE? 

• Made the feature easier to find 
• Added more format support 
• Made the feature available in Excel for Mac 

HOW IT WORKS 

Microsoft wanted to address customers’ frustration with Excel automatically converting data to specific formats. So, they gave users the ability to change Excel’s default behavior and disable specific types of automatic data conversions as needed. 

To do so, select File > Options > Data > Automatic Data Conversion, and then choose the conversion(s) that you’d like to disable.  

TIPS AND TRICKS 

• 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 
• Convert a continuous string of letters and numbers to a date 

• When you select the When loading a .csv file or similar file, notify me of any automatic number conversions check box, Excel displays 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 open the file once 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 

• The feature does not support disabling these conversions during macro execution. 

AVAILABILITY 

This feature is available to all users running: 

• Windows: Version 2309 (Build 16808.10000) or later 
• Mac: Version 16.77 (Build 23091003) or later 

NOTE: Content for this blog post was originally posted on Microsoft 365 Insider, October 19, 2023.