Microsoft finally fixes the Excel problem that caused scientists major headaches

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

Microsoft said it has implemented a new feature in Excel that allows users to disable automatic formatting of data types for specific columns or cells. This means that users can keep the original text of their data without worrying about Excel changing it. The function is available in Excel for Windows, Mac and the web, and will soon be rolled out to other platforms.

The Excel bug has been a source of annoyance and embarrassment for many scientists, who often had to manually fix 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, about a fifth of articles in top genomics journals contained errors caused by Excel.

Microsoft said it had been aware of the issue for a long time, but that it was difficult to fix without breaking 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 fix and praised Microsoft for listening to their feedback and addressing their needs. Many researchers said they would continue using Excel for their data analysis as it is a powerful and versatile tool that they are familiar with. However, some experts also advised scientists to be careful and attentive when using Excel and to always check the accuracy and consistency of their data.

If you are a scientist who works 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 might be surprised to find that Excel has a mind of its own and will often change your gene symbols to something else: dates.

That's right, Excel believes that some genetic symbols, such as DEC1 or MAR1, are actually dates, such as December 1st or March 1st. 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 what 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's 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 has finally released an update for Excel that allows users to disable the automatic date recognition feature. This means 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 possible 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. And most importantly, it will respect the names and symbols of genes established by the scientific community.

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

But there's 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 find the new settings in the Data submenu. This is a welcome addition to other features that warn you about potential errors in automatic conversions.