Excel changes some numbers or strings to dates to be "user-friendly" and ease the date entering process. However, Excel does so without actually asking you, and ironically, there is no option to turn this off. In this article, we're going to show you how to stop Excel from changing numbers to dates.
Basically, there are 2 ways to do this:
- Setting cell number format as Text
- Tricking Excel into thinking that you did not enter a number
Changing the Number Format
If you set a cell number format to Text, Excel will stop guessing what the entered value might be, and accept it as is. To change the Number Format of a cell:
- Select the cell or range.
- Press Ctrl + 1. Alternatively, you can also click the Format Cells option in the right-click menu.
- Select the option Text from the menu on the left.
Now you can enter any number or text you without Excel intervening.
Tricking Excel
You can convince Excel that you are not entering numbers by adding a different character before of the actual number or string. This way, you can prevent Excel from changing numbers into dates. Some common methods are:
- Adding an apostrophe (‘) character before the value. Doing so essentially has the same effect as setting Number Format to Text. The nice thing is you will not see apostrophe in the cell.
- An alternative method is adding a space character. However, we must mention that this is not the best method, as the space character can sometimes actually affect cell values or formulas.
- Another trick, especially for fractional numbers without an integer part, is adding a 0 and a space. This combination can act like the integer part and prevent the value from changing into a number. Also Excel removes zeroes and spaces automatically.
