Whenever you eneter numbers separated by a hyphen in Microsoft Excel, it automatically converts that data into date format. Suppose you type “2-2” in Excel it becomes “02-Feb” automatically, so to prevent this automatic formatting of numbers into date in Excel follow simple steps below.
Prevent Excel from Reformatting Numbers into Date
Method-1
Step-1
Select entire cells of a Workbook in Excel by pressing “Cmd” + “A” (Mac), “Ctrl” + “A” (Windows) and then right click on any cell and select the option “Format Cells…” from opened list
Step-2
Select “Number” tab, and from the left panel select “Text” category
Now, whenever you will enter numbers in a cell of this Worksheet, that will remain a number and will not be converted into date format.
Note: This change is only applicable for the Workbook/Worksheet in Excel for which it is selected.
Method-2
Simply type an apostrophe before typing numbers in a cell of Excel Workbook and look the effect after leaving that cell, apostrophe will hide automatically and the numbers will display as numbers.
Works for Excel 2011 on Mac.
Nice to have this tip for Microsoft Excel.