Entering fractions in Microsoft Excel isn’t exactly intuitive, but it is easy. Learn how to enter and display fractions as either fractions or as decimal values.
Fractions are enough to make you cry if you’re not a math whiz, but Microsoft Excel handles them very well. In fourth-grade math, you learned fraction basics: Fractions are a numerical representation of a part of a whole, such as ½, ¾, and so on. Then, you learned how to represent that same fractional value as a decimal value, and that’s how Excel evaluates fractions internally, as decimal values. In this article, I’ll show you how to enter fractions in a way that Excel can interpret them correctly. Then, I’ll show you how to format in ways that you can interpret.
SEE: Software Installation Policy (TechRepublic)
I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use an earlier version. You don’t need a demonstration file. I’ll be using the fraction 4/5 throughout the article, but feel free to experiment with other fractions.
About math fractions
There are three types of fractions, and Excel interprets them all:
- Proper fractions: The numerator is always less than the denominator; this fraction is always less than the whole, or mathematically speaking 1, and is represented by a decimal value. For instance, 4/5 is .8.
- Improper fractions: The numerator is always larger than the denominator; this fraction is always more than the whole, or 1, and is represented by a whole number and a decimal value. For instance, 9/5 is 1.8.
- Mixed fractions: The fraction includes a whole number. For instance, 1 4/5 is 1.8.
Excel handles fractions and displays them in fraction form. However, internally, Excel stores the fraction as a decimal value or a whole number and a decimal value. For example, Excel stores 4/5 as .8. Our minds can quickly visualize 4/5 but we don’t usually interpret .8 as 4/5.
How to enter fractions in Excel
Entering a fraction in Excel isn’t intuitive to most of us. If you enter 4/5, Excel will interpret the entry as a date and display it as a date, 4/5 or April 5. If you don’t realize it, that “date” will not evaluate as you expect. In order for Excel to recognize a fraction as a fraction, you must also enter the fraction’s whole number, which in most cases will be 0, followed by a space. Let’s try that now with the fraction 4/5:
- Choose any cell.
- Enter 0 4/5. Remember, three’s a space character between 0 and 4.
- Press Enter to see the results shown in Figure A.
Figure A
The formula bar displays the fraction as .8, even though it displays it as 4/5 at the cell level, which looks a lot like a date again, doesn’t it? In addition, the 0 isn’t to be seen anywhere at all. Excel needs that digit to interpret the entry as a fraction. However, if you were to enter 1 4/5, Excel retains the 1 digit and displays the fraction as 1 4/5 and stores the value 1.8.
Fractions don’t look so hot, but numerically, Excel evaluates them correctly.
How to format fractions in Excel
You might think this section will help you display fractions traditionally, by displaying the numerator over the denominator and using a smaller font, but that’s not going to happen in Excel. Instead, Excel’s fraction formats will help you round fractions. There are several to choose from and we’ll apply each to a properly entered fraction, 4/5.
Enter 0 4/5 into any cell and then copy it for a total of nine 4/5 fractions. Then, select the first one and do the following to apply a fraction format:
- Click the Number dialog launcher (on the Home tab).
- Click the Number tab if necessary.
- From the Category list, choose Fraction.
- In the Type list, choose the first option: Up to One Digit (1/4) (Figure B)
- Click OK.
Figure B
Repeat this process for each fraction, always choosing the next format. Figure C shows the results.
Figure C
The first three fractions accommodate 1, 2 and 3 digits in both the numerator and the denominator—so these first three formats are strictly about spacing. Notice that as the digit number goes up, the resulting fraction moves a bit to the left.
The halves format rounds 4/5 up to 1. This fraction has at least 1 half of a whole.
The next five formats display the fraction with different denominators, rounding as closely as possible. As you can see in B6, 4/5 doesn’t convert correctly to fourths, so it displays the next best thing 3/4, which is .75. That’s close to .8, but not quite .8. Internally, Excel is evaluating .8 in all these format options. The only two that represent .8 exactly are tenths and hundreds (8/10 and 80/100).
Some guidelines
As shown, not all fractions display what you entered. Here are a few things to keep in mind when entering and display fractions:
- If Excel can’t display the exact fraction, Excel will round the entry up to the nearest result.
- Excel converts fractions to the lowest denominator when entered. For example, if you enter 0 5/10, Excel will display 1/2.
If you don’t want Excel to convert the lowest denominator, you can create a custom format as follows:
- Open the Format Cells dialog as you did earlier by using the Number dialog launcher.
- Choose Custom in the Category list.
- Enter ??/10, into the Type control (Figure D). You could enter ?/10, but ??/10 accommodates a two-digit numerator.
Figure D
- Click OK to see the result in Figure E.
Figure E
Be sure to match the section component to the entered denominator. For instance, if you entered 0 5/100, you’d use the format ???/100. You could enter ?/100, but ???/100 would accommodate a 3-digit numerator.
If you want to enter fractions as fractions by displaying the underlying value, format the entries using one of the Number formats. In this way, you can quickly enter fractions without converting them to whole and decimal values in your head.
It’s up to you whether you need to format fractions differently than Excel’s first display. The way you’re using those fractions will help you make that decision.