This title sounds a bit silly, but I do believe many users must have experienced frustration when they tried to input a fraction number as simple as 1/2 in Excel.
When we input something like x/y, Excel assumes that we are inputting a date (whenever it can resolve it as date, e.g. 1/2 will be interpreted as either 1st of Feb or 2nd of Jan of current year depending on the setting of your computer; when the input cannot be resolved as date, e.g. 25/50, Excel turns it into text). I agree that such default behavior may annoy some users. Still we need to admit that we cannot please everyone most of the time.
There are two ways to tell Excel your intention of inputting fraction number:
- Through Formatting
- Through a trick
1. Through Formatting
Although it is a simple step, most of us forgot it until we had the date/text returned… I do that very often. Don’t you? 🙂
Note: Changing the formatting before input. We can’t convert a text string “25/50” into a fraction number simply by changing the format of the cell.
2. Through a trick of inputting a number 0 (zero & space) before the fraction number
The trick is simple. By inputting N x/y, we have given enough information to Excel on our intention of inputting fraction. Excel changes the format to Fraction automatically in this case. Although it is not perfect, I think it is better than changing the format. What do you think?
Note: We do not need to input 0 (zero) if there is already a whole number in the fraction number, e.g. 1 2/3.
Cross Reference: What we need to know about inputting Date in Excel?
wmfexcel 