We’d never thought we’d utter these words in our newsletter: A new update to Excel will make a lot of biomedical researchers very happy. You can now turn off the automated date formatting in Excel.

Why on earth would this be such a big deal to biomedical and other genetic researchers? Because of how genes are named and categorized and have been for many years. For example, gene symbols and names such as “SEPT2” (Septin 2) and “MARCH1” [Membrane-Associated Ring Finger (C3HC4) 1, E3 Ubiquitin Protein Ligase]. You see the problem there? “SEPT2” will change to “2-Sept” when put into Excel and “MARCH1” will convert to “1-March” by default and format the cells as dates, blitzing out the original text entirely. This has caused many years of issues in various research with up to 30% of research papers affected by this.  There has been all sorts of work arounds, with the scientific community finally throwing up their hands and just redoing how genes are classified to avoid this in the future.

Microsoft has finally got the message and now you have the ability to change Excel’s default behavior and disable specific types of automatic data conversions as needed.

To do so, select File > Options > Data > Automatic Data Conversion, and then choose the conversion(s) that you’d like to disable.

You can enable or disable the following options:

• Remove leading zeros from numerical text and convert to a number.
• Truncate numerical data to 15 digits of precision and convert to a number that may be displayed in scientific notation, if needed.
• Convert numerical data surrounding the letter “E” to a number displayed in scientific notation.
• Convert a continuous string of letters and numbers to a date.

When you select the “When loading a .csv file or similar file, notify me of any automatic number conversions check box,” Excel will now display a warning message when it detects that at least one of the optional automatic data conversions is enabled and about to occur when opening a .csv or .txt file, which will allow for better data consistency when files are shared between organizations. There is currently a known issue that this new feature does not support disabling these types of conversions during macro execution, so if you have Macros setup to execute on your data, you’ll need to keep an eye on things.

This feature is available in Excel on:

• Windows: Version 2309 (Build 16808.10000) or later
• Mac: Version 16.77 (Build 23091003) or later