How to Control Data Conversions in Excel for Windows and Mac


After years of struggling with Microsoft Excel’s internal conversion function, users can now disable Excel’s internal data conversion function. Excel tries to determine an input value’s information type– and, for most of us, that’s valuable. For example, if you enter a string that represents a date, Excel instantly transforms it into the date value. If you suggested to go into the date, terrific; if not, you’re out of luck. When entered, that entry is a date, and there’s no chance to get the initial string value back.

By default, there are four such Excel conversion behaviors; in 2021, Microsoft presented toggle alternatives, enabling users to pick how to execute 3 of them but not the string conversion. Excellent news: Windows and Mac variations now consist of an option that lets you disable the string conversion behavior in Excel. Below is more detail about this Excel problem and the option, which specifies to Microsoft 365.

Dive to:

Excel’s conversion feature typically makes errors

This Excel issue has been a problem to many users, especially the clinical community. In truth, we’re told that geneticists even altered the brief notation for genes instead of continue to work around the Excel string conversion behavior.

Let’s think about an example: March1 is the brief name for the Membrane Associated Ring-CH-Type Finger 1 gene, and it looks like the date March 1. When you get in March1, Excel converts it to a date immediately. This is because– in an effort to be handy– Excel assumes the value is really the date.

You may attempt pushing Ctrl + Z to undo the conversion, however it won’t work. Rather, Excel gets rid of the immediately applied date format, leaving the date’s serial value. At this moment, you can’t even transform the entry to text because the worth is now a number. The original string, MARCH1 and its meaning are gone.

Excel’s conversion feature transforms 4 kinds of input values:

  • Excel converts text entries that Excel can interpret as dates to date values.
  • Excel gets rid of leading absolutely nos and transforms the value to a number.
  • Excel truncates a numerical value of 15 digits or more to scientific notation.
  • Excel transforms mathematical digits surrounding the letter E to a clinical notation.

Figure A shows an example of each kind of input worths. You can see the worths went into and the worths Excel converted them to. For some users, this is a headache because it forces them to format cells before going into information; if they forget, there’s no workaround, and they should begin over.

Figure A

Excel sample conversion. Excel immediately transforms some input values, no matter your intents. As pointed out earlier, there’s no easy repair for undoing Excel’s automatic conversion for strings it transforms to date. You must format the target cell as Text before entering data. The exact same holds true with values that consist of leading nos and worths that consist of the letter E. The only conversion that users can easily repair after the truth is the conversion of big mathematical values to clinical notation. You can alter the format for these values to a Number format to remove the clinical notation.

New Excel alternatives allow you to disable the conversion function

Must-read big information protection

A couple of years earlier, Excel finally implemented 3 toggle alternatives that allow users to disable three of the four internal conversion habits; regrettably, these options didn’t aid with the string conversion behavior. Only after learning more about the geneticists’ workaround did Microsoft lastly include a fourth choice that enables users to disable internal string conversion. You do not have to be a geneticist to appreciate this last option; in fact, the internal string conversion issue frustrated more users than any of the other 3 conversion habits.

That final repair began presenting this summer, and everybody needs to have it by now. Lastly, you can control how Excel manages strings that might likewise represent dates.

To find these alternatives in Microsoft 365, follow these steps:

  1. Click the File tab.
  2. Pick Alternatives or choose More and after that Options.
  3. Click Information in the left pane. (Initially, the settings remained in the Advanced section; the Data section makes much better sense and ought to be easier to find.)
  4. In the Automatic Data Conversion area, you’ll see numerous alternatives (Figure B).

Figure B

Screenshot of how to change Excel's default internal conversion behavior. Change Excel’s default internal conversion behavior. You can now disable all the internal conversion behaviors. To disable all of the habits, you can uncheck the first alternative: Make it possible for All Default Data Conversions Below When Getting Into, Pasting, Or Loading Text Into Excel. Or, you can leave that alternative inspected and uncheck one or more of the other options, as required.

The last choice, When Loading A.CSV File Or Comparable File, Alert Me Of Any Automatic Number Conversions, is handy when you’re importing information that Excel might convert immediately. It’s a basic choice that signals you before transforming the inbound information. At that point, you can select to import without converting the data.

You can leave the default habits as is, which is the exact same as it’s constantly been. Or you can alter the default habits by using several of the 4 new alternatives. Nevertheless, this is an application-level habits, so once you set it for one workbook, Excel uses it to all workbooks. Maybe in the future, we’ll see this control extended to individual workbooks. In addition, there’s no chance to disable these conversion alternatives using a macro.


Leave a Reply

Your email address will not be published. Required fields are marked *