Microsoft adds Excel setting to fix scientific data mess-up
Microsoft has added a new setting to Excel, which addresses a years-long automatic reformatting problem that could ruin scientific data.
In a Microsoft 365 blog post on 19 October 2023, Excel team product manager Chirag Fifadra revealed the Automatic Data Conversion settings the company had been testing since last year were now broadly available in Excel for Windows and MacOS.
“We wanted to address customers’ frustration with Excel automatically converting data to specific formats,” Fifadra said.
“So, we have now given you 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.”
The new settings allow users to 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.
New Excel Automatic Data Conversion options
The last option is great news for the scientific community, which has complained for years about Excel incorrectly reading human genes as dates.
With its wide range of data capturing and processing features, Excel is a popular tool among scientists — including those working with the human genome.
Each of the tens of thousands of human genes is given a name and symbol in the form of an alphanumeric code.
For example, the alphanumeric symbol for Membrane Associated Ring-CH-Type Finger 1 is “MARCH1”.
Typing this into an Excel cell will automatically convert it to 1 March.
A study in 2016 found that one-fifth of 3,597 published papers with genetic data had been affected by Excel errors.
The issue took so long to address and became so annoying that scientists decided to rename 27 human genes in 2020, to prevent the spreadsheet software from ruining their data.
To avoid this issue, the HUGO Gene Nomenclature Committee changed MARCH1 to MARCHF1. Similarly, SEPT1 is now SEPTIN1.
While researchers could simply change the data type, the problem was that they shared data in comma-separated values (CSV) files.
CSV files do not preserve any of the formatting features used in Excel to prevent the automatic conversion from a text string into a date.
Now read: Microsoft publishes tutorial to install Linux
Loading ...