Excel is one of the programs that gets used in a multitude of sectors, from banking and finance, to engineering and medicine. In the case of the latter, geneticists routinely struggled with the program’s habit of converting text into a date, causing gene naming errors to crop up in research papers.
Well, it’s taken years to happen but Microsoft has finally updated Excel to solve this issue once and for all.
As reported by the Verge, the original problem wasn’t as mundane as you might expect. If your work involves entering thousands of gene codes into a spreadsheet, you’d want it to handle them all correctly. However, prior to this update, Excel would automatically convert text entries into a date, if the code looked similar to one.
The solution used to be reformatting the cells to prevent this from happening but if the spreadsheet is then saved as a type that doesn’t retain the formatting, then the next person to use that data will open it up and get a bunch of dates.
Given the amount of research in genetics undertaken around the world, simple errors like this can easily grow to become a widespread and potentially harmful problem. The scientific community in question eventually resorted to altering the renaming of a number of human genes to prevent the situation from getting worse.
In a blog posted last week, Microsoft showed that it had finally responded to feedback from Excel users and released an update to the Windows and MacOS versions that allows you to disable the automatic renaming system.
(Image credit: Microsoft)
If you’re using the latest version of Excel, simply click File and then Options, in the bottom left-hand corner. Choose Data in the menu and you should see the new Automatic Data Conversion options. Uncheck the very last one and you’re good to go!
It’s worth noting that Google Sheets will also convert text entries into a date, if it looks like one, and the only way around this is to format the column or row as text before you enter any data.
Now before you think that all of this is just making a mountain out of molehill, the automatic data conversion problem in Excel was highlighted as far back as 2016. That means it’s taken Microsoft seven years to acknowledge and ultimately address the issue. And the solution was such a simple one, too, so goodness knows why it took so long to come about.
It makes waiting a month or two for a critical game patch to be released look like a blink of an eye, by comparison.