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:


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.

Downloadable Files: Excel File

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Change Formulas to Absolute or Relative References
Macro: This macro will change the formulas in cells to absolute or relative. It allows you to cha...
Make Users Enable Macros in Order to View a Workbook in Excel
Tutorial: Tutorial showing you how to make a user enable macros in a workbook in order to view the w...
Delete Duplicate Values in All Versions of Excel
Tutorial: How to delete duplicate values from a data set in all versions of Excel. This includes Ex...
Stop Tables from Auto-Filling Formulas Down in Excel
Tutorial: Two simple ways to stop Tables from auto-filling formulas in new columns. Auto-filling fo...
Partial Match Lookup with Numbers in Excel
Tutorial: Perform lookups on numbers with partial matches. For instance, find the first number that...
Input Really Long Numbers into Excel
Tutorial: How to input any length number in Excel and have it remain visible. Excel truncates any ...