Stop Excel from Changing Numbers to Dates or Anything Else

Add to Favorites

How to stop Excel from automatically changing numbers into anything other than what you typed, such as changing a fraction into a date when you enter it into a cell.

Apostrophe Method

Using the apostrophe is the preferred way to keep numbers looking the same after you enter them, at least as far as I am concerned.  This way, if someone changes the formatting back to General and goes to edit a cell, it will still look the same way instead of being auto-formatted.

Type an apostrophe in the cell:

Then type the number and press Enter:

Notice that the apostrophe does not appear in the cell after we press Enter; however, if we go to edit the cell, you can see that it is still there:

Here is a sample list:

If we entered them without an apostrophe, they would have looked like this:

Using this method, we will also still be able to use MATCH and VLOOKUP with the numbers as Excel will ignore the apostrophe.

Format as Text Method

If you know that you will be entering a list of numbers, simply format the cells as Text before you go to enter the data.  This way, everything will look exactly the same as how you typed it.

Select the desired cells > go to the Home tab and click the drop down menu in the Number box and select Text:

Enter what you want:

If we just entered the numbers regularly, we would have gotten this:

Notes

Both methods will work.  I prefer to use the apostrophe method since it's harder for a user down the line to mess it up.

Make sure to download the sample workbook that accompanies this tutorial so you can better see how this works in Excel.

Like this tutorial? Follow us on Google +

Downloadable Files: Excel File