Input fraction number in Excel

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.

Excel Tip - Input Fraction.gif

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:

  1. Through Formatting
  2. Through a trick

1. Through Formatting

Excel Tip - Input Fraction1.gif

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

Excel Tip - Input Fraction2.gif

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?

This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s