Oct 22, 2023 16:00:00

Fixed the automatic conversion function that caused Microsoft Excel to incorrectly convert research data.

The automatic conversion function of Excel, Microsoft's spreadsheet software, automatically converts some inputs, such as numbers and dates, into specific types of data. Although this feature is intended to make data entry quick and easy, it has been

pointed out since 2016 that this feature is a cause of input errors in genetic research. However, this automatic conversion feature has finally been fixed.

Control data conversions in Excel for Windows and Mac
https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac

Microsoft fixes the Excel feature that was wrecking scientific data - The Verge
https://www.theverge.com/2023/10/21/23926585/microsoft-excel-misreading-dates-human-genes-conversion-fixed

For example, Excel's automatic conversion function converts 'SEPT2', the name ( gene symbol ) of ' Septin 2 ', a type of protein present in human cells, to '2-Sep (September 2nd)', the date. This was seen as a problem because it could cause unintended conversions, such as converting to . Errors caused by Excel's automatic conversion function were found in 704 of the 3,597 genetic research papers published in 2016.

Human gene names forced to change due to ``Excel's meddling'' - GIGAZINE

Chirag Fifadra, a product manager at Microsoft's Excel development team, announced that Excel's automatic conversion function has been fixed. Microsoft released an initial version in July 2022 with a feature to fix the automatic conversion feature in the Windows version of Excel, but it has further updated this feature to support more data formats. The Mac version of Excel has also been updated to include the same functionality.

If Excel automatically converts data in a certain format, you can disable the function that automatically converts certain types of data by changing some settings. To configure settings, click File → Options → Data → Automatic Data Conversion. Then, at the top of the field, you will see 'Enable all default data conversions below when entering, pasting, or loading text into Excel.' 'Enable all options', allowing you to disable multiple options. By default, all options seem to be enabled.

There are five items that can disable automatic conversion:

・Remove leading zeros from numeric text and convert it to numeric values.
- If necessary, numeric data is truncated to 15 digits of precision and converted to numbers that can be displayed in scientific notation.
・Convert numerical data around the letter 'E' to numbers displayed in scientific notation.
・Convert a continuous string of letters and numbers into a date.
- Notify automatic numeric conversion when loading .csv files or similar files.

If you enable the fifth setting, if Excel detects that at least one of the other automatic data conversion options is enabled and you are about to perform automatic data conversion when you open a file, It seems that a warning message will be displayed.

However, the above options do not work during macro execution, so be careful.

Please note that these functions can be used with the following versions of Excel or later.

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