Microsoft solved the problem because of which scientists have been tormented for many years: Excel forcibly turned the names of some genes into dates

And these genes were even officially renamed. Now special "scientific" functions have been introduced to Excel

This material was first published on August 6, 2020 and updated on October 24, 2023.

Microsoft on October 19 introduced the update of the Excel program, which should finally solve the problem, known since at least 2004: turning the names of some genes into dates during their forced formatting. Not only genes, but also some digital -boom codes can take a similar conversion.

The names of genes usually consist of several letters (most often it is an abbreviation indicating the type of mutation or the purpose of the gene) and can also be complemented by numbers. Some of the previously existing names were similar to the names of the months of the year, which is why Excel, which many scientists around the world still use, interpreted them as dates. For example, when recording an Excel cell, the March1 Gena Clot automatically converted it into “March 1”.

Although in Excel, when filling out the table, you can manually turn off automatic formatting, this did not solve the problem: when the file was opened by another person whose default function was turned on, the data was still transformed.

The problem was massive. In 2016, a study was conducted, the authors of which studied 3597 scientific works published in the reviewed scientific journals, and in about every fifth found errors caused by automatic formatting of data in Excel.

Unable to influence Microsoft, the genes nomenclature committee in 2020 recommended simply changing the names of several dozen human genes. In total, the committee issued recommendations for the renaming of 27 genes. So, for example, March1 became Marchf1, and Sept1 - Septin1.

Now, the manufacturer of the tabular editor has tried to finally solve the formatting problem by giving the user the opportunity to disable different types of transformation. So, in the “File> Settings> Data> Automatic Data Convert” menu, several “checkmarks” appeared that allow not only to disconnect the conversion of text like March1 on the date, but also to turn off the “scientific” number of the number, due to which digital-boom codes like 231000E13 could understand as numbers (2.31 × 10¹³). Microsoft notes that the proposed solution will work only if there are no macros in the document.

Excel is originally not intended to analyze scientific information, but scientists continue to use the program due to its wide distribution and ease of use. And sometimes this leads to other scandalous errors.

So, in 2020, due to the use of Excel, the British public health service lost the results of about 16 thousand tests per Covid. The service workers manually collected in Excel the test results sent by him from laboratories in a simple text format CSV, and imperceptibly reached a millionth limit for the number of lines in the table. As a result, the “extra” test results were lost.

"Jellyfish"