In 2020, researchers opted to modify the alphanumeric symbols representing genes to circumvent an Excel feature that mistakenly recognized their names as dates, automatically reformatting them. Recently, an Excel team member announced that an update is being introduced for both Windows and macOS to address this issue.
Excel’s auto-conversion feature aims to simplify the entry of common data types, like numbers and dates. However, for scientists using shorthand for clarity, this feature could inadvertently distort published, peer-reviewed data, as highlighted in a 2016 study.
Microsoft elaborated on this update in a recent blog post, introducing a checkbox titled “Convert continuous letters and numbers to a date.”
Its function is self-explanatory. This enhancement complements the Automatic Data Conversions settings introduced the previous year. These settings alert users before Excel’s auto-conversion kicks in, allowing them to open their files without the automatic conversion, ensuring data integrity.
How does it work?
Microsoft has taken steps to alleviate user frustrations stemming from Excel’s tendency to auto-convert data into specific formats. Users now have the flexibility to modify Excel’s default settings and deactivate certain automatic data conversions as required.
To implement these changes, navigate to File > Options > Data > Automatic Data Conversion. From there, you can select which conversion(s) you wish to deactivate.
Additionally, Microsoft has provided some useful pointers:
- Users have the option to enable or disable the following features:
- Remove leading zeros from text-based numbers and transform them into numerical format.
- Limit numerical data to 15 digits of precision and, if necessary, convert it into a number that might be represented in scientific notation.
- Transform numerical data containing the letter “E” into a format displayed in scientific notation.
- Change a continuous combination of letters and numbers into a date format.
- By selecting the “When loading a .csv file or similar file, notify me of any automatic number conversions” option, Excel will prompt a warning if it identifies that any of the optional auto-conversions are activated and about to be applied when opening a .csv or .txt file. This alert offers users the choice to open the file once without executing the data conversion.
To truly grasp the enhanced control over data conversions based on the settings you’ve adjusted, it’s recommended to experiment with the following scenarios:
- Enter data directly into an Excel cell.
- Copy data from external sources, such as a website, and then paste it into Excel.
- Open files with extensions .csv or .txt in Excel.
- Execute ‘Find and Replace‘ operations within Excel.
- Navigate to Data > Text to Columns and utilize the Convert Text to Columns Wizard.
Given that this feature retains the input data in a text format, you might encounter a green triangle indicating a “Number stored as text” error. This is a standard notification. Should you wish to dismiss this error, simply choose “Ignore error” from the dropdown menu. It’s also worth noting that, due to the data being stored as text, you might face limitations when attempting to use it in mathematical functions.
This enhanced feature is accessible to users with the following versions or later:
- For Windows users: Version 2309 (Build 16808.10000)
- For Mac users: Version 16.77 (Build 23091003)
In Microsoft’s recent blog post, they highlighted certain limitations associated with the new Excel feature. Specifically, to prevent unwanted data conversions, Excel saves the data as text.
This approach, while effective in preserving the original data format, means that users might encounter challenges when attempting to use this data in subsequent calculations.
Additionally, there’s a recognized limitation: when executing macros, users cannot deactivate these automatic data conversions. This is an important consideration for those who frequently rely on macros for their Excel operations.
