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?


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.