Excel allows you to enter fractions into cells, provided you preface the entry with a zero and a space. Thus, if you wanted to enter a fraction such as 18/28, you would enter 0 18/28 into the cell. (If you just enter 18/28, then Excel assumes you are entering either a date or text.)
When you enter your fraction, Excel does two things during the parsing process. First, it formats the cell as a fraction, based on the number of digits in the denominator of the fraction. In the case of the fraction 18/28, there are two digits in the denominator, so the cell is automatically formatted as a fraction of up to two digits. (See the Number tab of the Format Cells dialog box.)
The second thing that happens is that Excel converts the number into its decimal equivalent. In other words, Excel stores the number internally as 0.642857142857143, which is what you get when you divide 18 by 28. At this point, the fraction no longer exists; the number is a decimal value.
After you enter any value into Excel, it automatically recalculates your worksheet. With the parsing done, and the new value entered, Excel recalculates and redisplays values. Remember—the value in the cell is now 0.642857142857143, and to redisplay the value, Excel sees that it is supposed to use a fraction of up to two digits. The smallest fraction it can do this with is 9/14. Thus, this is what Excel displays in the cell—9/14 instead of 18/28.
Because of the parsing process that Excel follows, there is no way that you can force Excel to remember your fractions exactly as you entered them. After all, Excel doesn't store fractions, it stores decimal values. You can, however, change the format used by Excel to display the value in a particular cell. For instance, if you wanted the cell into which you entered 18/28 to display the fraction with 28 as the denominator, then you could follow these steps:
Figure 1. The Number tab of the Format Cells dialog box.
Again, remember that this only changes the display of the values in Excel, not the actual values themselves—they are still decimal values. The only way to have Excel remember exactly what you entered is to enter the fraction as text (format the cell as Text before making your entry), but there is a drawback to this. Once entered as text, you cannot use the fraction in any calculations.
If this is a problem for your needs, then you may want to consider putting the numerator in one cell and the denominator in another cell. This provides a way to remember what you entered, but still be able to use the numerator and denominator in a formula.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12043) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Stopping Fractions from Reducing.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Hiding information in one or more cells can be a challenge. This tip presents several different techniques that can help ...
Discover MoreIf you have a range of numeric values in your worksheet, you may want to change them from numbers to text values. Here's ...
Discover MoreWant to maintain the formatting used in one cell when you use formulas to reference that text in another cell? The answer ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-08-05 21:43:47
Peter
There are two alternatives, one is to automate the process Allen describes,
and the other is to enter the fraction as a formula, like =12/18 . Formulas can be displayed or hidden by keying ctrl-`
To automate, you would start with the cell formatted as text before entering the fraction.
Then using the Worksheet_Change() macro, update the cell format to a custom fraction based on the denominator.
Then evaluate the cell's text as a number.
2020-08-04 11:12:51
Peter Atherton
John, I tried it myself. 0 18/24 and it returned 9/24. Then I used Excel's format as decimal and it diplays 2/3. Using this format before entry is problematical. Allen's tip is the best way to go or always use the highest format available. Use a format based on the Custom Format # ??/??
2020-08-03 05:33:38
Col Delane
Steps 5 & 6 can be replaced with:
5. Select the "Fraction" category
6. Select the Type option required from the list provided
2020-05-31 19:11:38
John Mann
Interesting. I just tried that oout in Excel 2010. I formated a large area as Fraction (plenty of room to mess around). I then entered "0 18/28" (without the quotes) and when I hit ENTER, the result was "2/3" in the cell (again without the quotes). When I looked at the formula bar up top, the number present was 0.642857142857143 - the same as in the tip. For the result to display as 2/3, the actual number shown in the formula bar should have been 0.666666666666667 or something close to that.
2018-01-25 20:51:17
easy
helpful thank you!
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments