Stop Excel from Changing Numbers to Dates or Anything Else

Add to Favorites
Author: don

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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

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 ...
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...
Input Really Long Numbers into Excel
Tutorial: How to input any length number in Excel and have it remain visible. Excel truncates any ...
Partial Match Lookup with Numbers in Excel
Tutorial: Perform lookups on numbers with partial matches. For instance, find the first number that...
MODE() - Find Most Repeated Value in a List in Excel
Tutorial: The MODE() function in Excel allows you to quickly find the most repeated or frequently oc...
Tutorial Details
Downloadable Files: Excel File
Similar Content
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 ...
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...
Excel Forum