Microsoft Excel finally lets you disable the auto-conversion of data into dates
An Excel autocorrect feature that has driven geneticists crazy for years has finally been rectified.
Even if you're not a scientist studying genentics, you've surely come across this issue. When entering data into Microsoft Excel, the spreadsheet will sometimes interpret alphanumeric formulas as dates and automatically format it as such. Genes symbols often have names that are incorrectly assumed to be dates, such as SEPT2 or MARCH1, which have created a headache for scientists just trying to input their data. So much so that a 2016 study found one-fifth of research papers using data compiled in Excel to be incorrect or corrupted. It got to the point that scientists actually began renaming genes to avoid the infuriating auto-conversion.
Now, Microsoft finally has a fix. In an announcement last week, the Microsoft 365 team released a setting that enables Excels users to change the overly helpful default behavior.
You May Also Like
How to turn off Automatic Data Conversion
To turn off this setting in Excel, go to File > Options > Data, then scroll down to Automatic Data Conversion. Then uncheck the setting that says "Enable all default data conversions below when entering, pasting, or loading text into Excel." There are also sub-settings to disable the removal of leading zeros, truncating numerical digits to a certain format, and the auto conversion of numerical data around the letter "E," as well as the aforementioned auto-conversion of continuous letters and numbers into a date.
Additionally, there's an option to notify the user of any auto-conversions when importing a .csv file into Excel. However, Microsoft said in the announcement, there are a few exceptions. Excel saves the data as text, meaning it might not work for mathematical calculations. Also, there's a known issue that doesn't support the disabling of auto-conversions when running macros.
That said, it's a long overdue setting that's sure to make geneticists happy and hopefully prevent future errors and naming predicaments.
Topics Apps & Software Microsoft
Cecily is a tech reporter at Mashable who covers AI, Apple, and emerging tech trends. Before getting her master's degree at Columbia Journalism School, she spent several years working with startups and social impact businesses for Unreasonable Group and B Lab. Before that, she co-founded a startup consulting business for emerging entrepreneurial hubs in South America, Europe, and Asia. You can find her on X at @cecily_mauran.
