The autocorrect feature in Excel that has been driving geneticists crazy for years finally corrected.
Even if you’re not a genetic scientist, you’ve probably encountered this problem. When you enter data into Microsoft Excel, a spreadsheet sometimes interprets alphanumeric formulas as dates and automatically formats them as such. Gene symbols often have names that are mistaken for dates, such as SEPT2 or MARCH 1, which caused headaches for scientists trying to enter their data. Except that 2016 study found that one-fifth of research articles using data compiled in Excel are incorrect or corrupt. It got to the point where scientists actually started changing gene names to avoid annoying automatic conversion.
Now Microsoft has finally found a solution. In an announcement last week, the Microsoft 365 team released a setting that allows Excel users to change the overly useful default behavior.
How to disable automatic data conversion
To disable this setting in Excel, go to File > Options > Data, then scroll down to Automatic Data Conversion. Then uncheck the “Enable all default data conversions below when entering, pasting, or loading text into Excel” setting. There are also subsettings to disable leading zero removal, truncation of numeric digits to a specific format, and automatic conversion of numeric data around the letter “E”, as well as the previously mentioned automatic conversion of continuous letters and numbers to date.
Finallyyyyy.
Source: Microsoft
Additionally, it is possible to notify the user about any automatic conversions when importing a .csv file into Excel. However, Microsoft stated in the announcement that there are a few exceptions. Excel saves data as text, which means it may not work for mathematical calculations. Additionally, there is a known issue that does not support disabling automatic conversions when running macros.
That said, this is a long overdue arrangement that will certainly make geneticists cheerful and hopefully prevent future mistakes and naming hassles.
