Stop Excel from automatically converting dates with these methods

One of the most annoying things that happens in Excel is when you type something, and the data gets converted to a date format and looks nothing like what you typed.

I’m using Excel almost every day, but I’m not an Excel guru. I would dare say I’m an advanced Excel user because I’ve used the program for more than 25 years, but honestly, I still suck.

I feel I’m not the only one in this category.

Anyway, one of the things that annoys me most is when Excel decides to do something automatically, “to help me out”, but actually misses the mark.

One of those moments is when I just want to type something in a cell that looks like a date, but it’s not actually one. Naturally, I don’t want Excel to transform the text into a date. Not only that, but I don’t want a different date format. I don’t want something like this:

There are multiple ways to solve this, each with its Pros and Cons:

Method 1 - Disable automatic data conversion in settings

This method has the advantage that it works like an On and Off switch. Once disabled, it will never work again in Excel until you re-enable the setting.

The downside? It doesn’t catch all situations, so some automatic data conversion will still happen.

This is how to toggle the setting: Click the File menu > Choose Options > Select Data > Scroll to Automatic Data Conversion.

Here you have a bunch of options. I would first suggest disabling Convert continuous letters and numbers to a date. See if it’s enough, if not go back and disable the other options as well.

Method 2 - Change the cell type before you add text

This method works well, but you have to change the cell type before you add contents.

Just select the cell, or the cells you want to add normal text to, then go to the Home tab, find the Numbers panel, and in the top dropdown menu, change the cell type to Text.

You can now type whatever you want and it will be stored as text.

Method 3 - just add an apostrophe before you type into a cell

This is probably the most straightforward method. Just begin typing with an apostrophe (‘) before the actual content. The cell type will be automatically converted to text.

Yes, it’s that simple.

Mention: when you paste contents into an Excel cell or multiple cells most likely the cell type will change automatically. You can circumvent this by right-clicking before you paste, then selecting the paste type that matches the destination formatting. Or paste as plain text, like I’ve shown before.

This is it for today. What other Excel hacks or tricks do you know and would like to share with the world?

Previous articles you may have missed

Programming is serious business, but programmers do have a sense of humor. This is why this neat Windows trick is called God Mode. Can you guess what it is? If you’re curious check out the explanation.