Microsoft is finally fixing an Excel data bug that had scientists fuming

Microsoft Excel
(Image credit: / monticello)

Microsoft has improved the Automatic Data Conversion settings in its Excel spreadsheet software, fixing an issue which had been causing scientists major headaches for several years.

In an announcement, Excel Product Manager Chirag Fifadra said: “We wanted to address customers’ frustration with Excel automatically converting data to specific formats.”

For a long time, scientists were forced to rename several genes for the purpose of preventing Excel from misreading them as other formats, such as dates.

Excel will now stop misreading some of your cells

Now, there is a new setting within the spreadsheet software that prevents the automatic conversion of data, which has so far proven itself (very) unuseful for some users.

Scientists working with gene codes, for example, can go to File > Options > Data > Automatic Data Conversion to disable certain types of automatic conversions. Fifadra gave the following four examples as areas where users can enable or disable automatic conversions:

  • 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.

Fifadra noted that, in some cases, numbers may be stored as text in order to preserve leading zeros, for example, which creates an error message. “This is expected,” says the announcement, which provides instructions on how to ignore the error message.

The changes have now been rolled out to Excel users on both Windows (Version 2309, Build 16808.10000) and macOS (Version 16.77, Build 23091003).

More from TechRadar Pro

Craig Hale

With several years’ experience freelancing in tech and automotive circles, Craig’s specific interests lie in technology that is designed to better our lives, including AI and ML, productivity aids, and smart fitness. He is also passionate about cars and the decarbonisation of personal transportation. As an avid bargain-hunter, you can be sure that any deal Craig finds is top value!