Microsoft finally fixes Excel issue that caused major headaches for scientists

After years of frustration, researchers can finally breathe a sigh of relief. Microsoft has announced that it has fixed a long-standing problem with Excel that caused some scientific data to be corrupted or misinterpreted. The bug involved Excel automatically converting certain gene and protein names to dates or numbers, such as «MARCH1» becoming «1-Mar» or «SEPT2» becoming «2-Sep.» This created errors and confusion for many scientists working with genomic or proteomic data.

Microsoft dijo que ha implementado una nueva característica en Excel que permite a los usuarios deshabilitar el formato automático de tipos de datos para columnas o celdas específicas. Esto significa que los usuarios pueden conservar el texto original de sus datos sin preocuparse de que Excel lo cambie. La función está disponible en Excel para Windows, Mac y la web, y pronto se implementará en otras plataformas.

The Excel bug has been a source of annoyance and embarrassment for many scientists, who often had to manually correct the errors or use workarounds to avoid them. Some researchers even published papers with incorrect gene or protein names, which could have serious implications for the reproducibility and validity of their results. According to a 2016 study, approximately one-fifth of articles in leading genomics journals contained errors caused by Excel.

Microsoft said it had been aware of the problem for a long time, but that it was difficult to fix it without disrupting functionality that many users rely on. Excel is designed to make data entry and analysis easy and intuitive, and automatic formatting of data types is one of its key features. However, Microsoft recognized that this feature does not always work well for scientific data and that it needed to provide more control and flexibility to users.

The scientific community welcomed news of the solution and praised Microsoft for listening to their feedback and addressing their needs. Many researchers said they would continue to use Excel for their data analysis, as it is a powerful and versatile tool with which they are familiar. However, some experts also advised scientists to be careful and vigilant when using Excel and to always check the accuracy and consistency of their data.

If you are a scientist working with genes, you know how important it is to have a clear and consistent way of naming them. That’s why there is a standard system for giving each gene a name and a symbol, which is a short combination of letters and numbers. For example, the gene that codes for the enzyme that breaks down lactose is called lactase and its symbol is LCT.

But what happens when you try to enter these symbols into Excel, the popular spreadsheet software that many scientists use to store and analyze their data? You may be surprised to discover that Excel has a mind of its own and will often change your gene symbols to something else: dates.

That’s right, Excel thinks that some genetic symbols, like DEC1 or MAR1, are actually dates, like December 1 or March 1. And it will automatically format them as such, without asking you or giving you the option to stop it. This can cause a lot of confusion and frustration, as well as errors and inconsistencies in your data. Imagine if you are looking for the expression level of DEC1 in a sample, but Excel shows you 01/12 instead. How would you know which gene you are dealing with?

This problem has been plaguing scientists for years, and has even led to some genes being renamed to avoid Excel meddling. For example, DEC1 is now known as BHLHE40 and MAR1 is now known as MARCH1. But not all genes have changed and some new ones could still fall victim to Excel’s date conversion.

Fortunately, there is good news. Microsoft finally released an update for Excel that allows users to turn off the automatic date recognition feature. This means that you can enter your gene symbols without worrying about them being transformed into dates. To do this, you need to go to File > Options > Advanced > Editing Options and uncheck the box that says «Auto flash fill». Then you can enjoy working with your genes in peace.

This update is a welcome relief for many scientists who have been struggling with Excel date conversion for a long time. It will save them time and effort and avoid potential errors and misunderstandings in their data. It will also make it easier to share and compare data with other researchers using different software or platforms. Most importantly, it will respect the gene names and symbols established by the scientific community.

If you work with data, you probably know the frustration of Excel messing up your numbers with unwanted conversions. For example, if you enter a gene name as DEC1, Excel might change it to a date format such as 1-Dec. This can cause serious problems for researchers and analysts who rely on accurate data. In fact, a 2016 study found that Excel errors affected 20% of 3597 published articles that used gene names.

But there is good news: Excel finally fixed this problem with a simple update. Now you can turn off automatic conversions in Excel and keep your data as you expected. To do this, go to Excel Options and look for the new setting in the Data submenu. This is a welcome addition to other features that warn you about possible errors in automatic conversions.