Excel no longer messes things up for scientists trying to store genetic codes in a spreadsheet

Double exposure of creative DNA hologram on laptop background
(Image credit: Getty Images)

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.


Best gaming monitor: Pixel-perfect panels.
Best high refresh rate monitor: Screaming quick.
Best 4K monitor for gaming: High-res only.
Best 4K TV for gaming: Big-screen 4K PC gaming.

Nick Evanson
Hardware Writer

Nick, gaming, and computers all first met in 1981, with the love affair starting on a Sinclair ZX81 in kit form and a book on ZX Basic. He ended up becoming a physics and IT teacher, but by the late 1990s decided it was time to cut his teeth writing for a long defunct UK tech site. He went on to do the same at Madonion, helping to write the help files for 3DMark and PCMark. After a short stint working at Beyond3D.com, Nick joined Futuremark (MadOnion rebranded) full-time, as editor-in-chief for its gaming and hardware section, YouGamers. After the site shutdown, he became an engineering and computing lecturer for many years, but missed the writing bug. Cue four years at TechSpot.com and over 100 long articles on anything and everything. He freely admits to being far too obsessed with GPUs and open world grindy RPGs, but who isn't these days?