Microsoft Excel can automatically detect dates, times, currencies, and more from text and convert them into a parsed format for use in formulas. However, Excel often gets conversions wrong, especially when it comes to dates, which Microsoft is now working to address.
Microsoft revealed this week that it’s working on a new option to turn off Excel’s automatic data conversions, which is now available in the Office Insider testing builds on Windows. The new setting is available from File > Options > Advanced > Automatic Data Conversion. You can turn off each conversion type individually, and there’s also a setting to show a warning before converting data in an imported .CSV document.
The setting is a much-needed addition to Excel, since automatic conversion can be heavy-handed. For example, typing in ID numbers or other identifiers that have leading zeroes can cause Excel to remove the starting zeroes. Excel also converts dates like “July 7, 2022” into date strings like “7-Jul-22.”
Microsoft said in a blog post, “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. While we aren’t changing Excel defaults, we are giving you the ability to 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.”
Excel’s automatic data conversions, especially related to dates, have been the butt of jokes for years. The HUGO Gene Nomenclature Committee, the group that names human DNA genes, renamed several genes in 2020 because Excel wouldn’t stop converting them to dates.
excel: is that a date?
me: 57.39 is very much not a date
excel: strong date vibes to me
me: h-how
excel: fixed it
me: 57/39/2020?
excel: you’re welcome
— slate (@PleaseBeGneiss) November 17, 2020
Even though date conversion is the most contested feature, there isn’t an option to turn that off yet — Microsoft says that is “planned but not yet available.” The new settings also don’t work with macros right now.
The new data conversion options are available to Office Insiders running Excel version 2207 (Build 15427.20000) or later on Windows. There’s no timeline right now on when it will roll out to everyone with Excel, and Microsoft says the feature is still in the works for Mac. The company didn’t mention when (or if) it will be available in web Excel or the mobile apps.
Source: Office Insider Blog
