How To Stop Automatic Data Formatting In Excel

Someone wrote to me recently complaining that “Excel keeps changing my numbers to dates!” Obviously, they wanted to know why and if they could fix it and fix it permanently.

I have to be honest so full disclosure here – I do not use Excel all that often and so I almost always do not have an answer to inquiries and I have to do some research to figure out if something can be fixed and how. It is a win-win because then the person asking learns something new and so do I!

Throughout my research, I have now learned that at times it can be a real pain to import data into your worksheets. As a matter of fact, it seems that it is a fairly common problem. Why? Because Excel is trained to detect formats and sometimes it just gets it wrong and assumes that certain numbers represent dates!

The person who inquired about this was having an issue with using 11/15 and it was being changed to a date. I think I have found an answer for the person who inquired and for you as well if you ever need it!

Follow the steps below to learn how:

  1. Open Excel.
  2. Select the columns or cells you would like to paste and select choose Text in the format drop-down on your Home tab.
  3. You can also press Cmd + 1 to select Text.
  4. Click OK.

Now that you have done that, please be certain that your data is pasted as text. How do you do this?

Follow the steps below to learn how:

  1. Go to your Home tab and click on the small arrow next to Paste and select Match Destination Formatting.
  2. You can also go to Edit | Paste Special and select Text prior to clicking OK.
  3. You should see small green triangles in the top left portion of your pasted cells.
  4. Select them and click on the warning sign and select Ignore Error.

Hopefully, this will take care of your problem!

Leave a Comment

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

Scroll to Top


Get great content like this delivered to your inbox!

It's free, convenient, and delivered right to your inbox! We do not spam and we will not share your address. Period!