Microsoft Excel users have long grappled with the platform’s internal data conversion, but now, there’s a solution. You can now turn off Excel’s internal data conversion feature.
Excel automatically decides the data type of your input, usually helpful. For example, if you type a string representing a date, Excel converts it into a date value. However, this can be problematic if it’s not what you intended, as once entered, the value becomes a date, and reverting to the original string is impossible.
By default, Excel has four conversion behaviors. In 2021, Microsoft introduced toggle options, allowing users to control three of them, excluding string conversion. The good news is that both Windows and Mac versions now have an option to disable string conversion in Excel. Find more details about this Excel issue and the Microsoft 365-specific solution below.
Excel’s Conversion Woes and Their Impact
The persistent problem of Excel’s conversion feature has been a major frustration for users, particularly in scientific fields. Surprisingly, geneticists have even altered gene notations to avoid dealing with Excel’s string conversion quirks.
Consider this example: the short notation “March1” for the Membrane Associated Ring-CH-Type Finger 1 gene resembles the date March 1. Upon entering “March1” into Excel, it automatically converts it to a date, assuming helpfulness. However, attempting to undo this conversion with Ctrl + Z fails, leaving a serial date value without the original string “MARCH1.”
Excel’s conversion feature affects four types of input values:
- Converts text entries resembling dates to date values.
- Removes leading zeros and converts the value to a number.
- Truncates numerical values of 15 digits or more to scientific notation.
- Converts numerical digits surrounding the letter E to scientific notation.
This poses a nightmare for users, especially those in scientific fields, as it necessitates formatting cells before data entry. Forgetting this step leaves users with no workaround, forcing them to start over.
Undoing Excel’s automatic string-to-date conversion lacks an easy fix. To avoid this, users must format the target cell as Text before data entry. The same applies to values with leading zeros and those containing the letter E. While fixing large numerical values converted to scientific notation is possible by changing the format to a Number format after entry.
New Options in Excel: Take Control of Data Conversion
A few years back, Excel introduced three toggle options empowering users to disable three of the four internal conversion behaviors. Unfortunately, these options didn’t address the persistent issue of string conversion. It was only after Microsoft learned about the workaround used by geneticists that they added a fourth option, allowing users to finally disable internal string conversion. This fix, addressing a problem that frustrated more users than the other three conversion behaviors, rolled out this summer and should now be available to everyone.
To take advantage of these options in Microsoft 365, follow these steps:
- Click the File tab.
- Choose Options or More, then Options.
- Click Data in the left pane (previously in the Advanced section, now in the Data section for better accessibility).
- In the Automatic Data Conversion section, you’ll find several options.
Now, you have the ability to disable all internal conversion behaviors. Uncheck the first option, “Enable All Default Data Conversions Below When Entering, Pasting, Or Loading Text Into Excel,” to disable all behaviors. Alternatively, keep that option checked and uncheck specific behaviors as needed.
The last option, “When Loading A .CSV File Or Similar File, Notify Me Of Any Automatic Number Conversions,” is particularly useful when importing data that Excel might automatically convert. It alerts you before making any conversions, allowing you to import data without automatic changes.
Keep in mind that these changes apply at the application level, affecting all workbooks once set. While currently unavailable for individual workbooks, future updates might address this. Additionally, there’s no macro-based method to disable these conversion options.