Microsoft Excel converting numbers into other formats has been a running joke for years, especially with date conversions, Microsoft rolled out a new setting last year that turns off data conversions like scientific notation and removing leading zeroes, and now an additional setting to turn off date conversion has arrived. Also, the options are available on Excel for Mac for the first time.
Microsoft said in a blog post, “We wanted to address customers’ frustration with Excel automatically converting data to specific formats. So, we have now given you the ability to change Excel’s default behavior and disable specific types of automatic data conversions as needed.” The settings for automatic data conversion moved, which Microsoft is hoping will make them easier to find. The options are now located at File > Options > Data > Automatic Data Conversion, instead of hiding in the Advanced menu.
The initial rollout last year included options for displaying large numbers in scientific notation, converting numbers around the letter “E” to a number in scientific notation, and removing leading zeroes. Microsoft has now added an option to stop converting continuous letters and numbers to a date, which has been a long time coming. Excel has never been great at guessing when a string is actually a date or not, so the option to turn that off entirely might save you some headaches.
Microsoft Excel can notify you when it detects data that needs to be converted when opening a .CSV file or other non-native spreadsheet format. That option is also available from File > Options > Data > Automatic Data Conversion. Microsoft is also rolling out all the data conversion options to Excel for Mac for the first time — last year’s rollout was limited to Excel for Windows.
The new data conversion options are available in Excel for Windows version 2309 (build 16808.10000) or later, and Excel for Mac version 16.77 (build 23091003). They are not yet live in the mobile versions of Excel or the Microsoft 365 web app version. Hopefully, that will happen soon, but Microsoft has not provided a timeline for that.
Source: Microsoft 365 Insider Blog
