In 2020, scientists faced a conundrum: the alphanumeric symbols they used to represent genes were being misinterpreted by Excel, transforming them into dates and causing havoc in the process. Faced with this dilemma, many scientists decided to rework their gene symbols rather than grapple with Excel’s perplexing behavior.
Now, it seems there’s a light at the end of the tunnel. The Excel team at Microsoft has rolled out a crucial update for both Windows and macOS, aimed at rectifying this long-standing issue.
Excel’s automatic data conversions were initially designed to streamline the input of frequently used data types, such as numbers and dates. However, for scientists who often employ quick shorthand for gene symbols to enhance readability, these automatic conversions turned into a source of frustration.
A 2016 study brought this issue to the forefront, highlighting how Excel’s well-intentioned assistance could inadvertently distort and disrupt published, peer-reviewed scientific data.
The Solution: Excel’s New Checkbox
In response to the persistent problem, Microsoft introduced a pivotal update. This update, detailed in a recent blog post, introduces a significant change in the form of a checkbox labeled “Convert continuous letters and numbers to a date.” Essentially, this checkbox allows users to control the very feature that caused the trouble in the first place.
This recent update builds upon the foundation of the Automatic Data Conversions settings introduced by Microsoft last year. These settings included the option for Excel to provide a warning when it’s about to execute automatic data conversion.
Users are then given the choice to load their file without automatic conversion, ensuring that no inadvertent alterations are made to the data.
Caveats and Known Issues
While this update comes as a boon to scientists, Microsoft’s blog post wisely includes a few caveats. For instance, Excel will bypass the conversion by saving the data as text. However, this means that the data may not be suitable for calculations later on. Additionally, a known issue persists, where users cannot disable the conversions when running macros.
With this update, Microsoft takes a significant step toward resolving a persistent issue that has plagued scientists for years. Now, researchers can work with their gene symbols without fear of unwanted and disruptive transformations, safeguarding the integrity of their scientific data.
