You can find two types of fractions in Excel: simple fractions (e.g. 4/5) and decimal fractions (e.g. 1.34). In this lesson, I will show you how to use them.
Decimal fraction (12.432) has the decimal part and the fractional part. They are separated by a dot (comma in some countries).
Excel will automatically recognize the value as a decimal as soon as you confirm your entry. By default, when you type a fraction, Excel will treat it as of a General type, so each number will be saved with different precision.
Number of decimal places
If you want these numbers to be treated as decimal fractions, select them and open the Format Cells window (Ctrl + Shift + F). In the Number tab, in the Category box, click Number from the list.
Set the number of decimal places (in this example I used 2 decimal places).
Notice that the value in cell B3 has been rounded. If you click it, you will see that in the formula bar it is still 78.5678. It means that when you change the display precision the information is not lost.
If you want to change the number of decimal places, you can perform the same operation again, this time choosing a different number.
However, there is another, faster method you can use to achieve the same result. You can change precision by using one of two buttons: Increase Decimal and Decrease Decimal. You’ll find them in HOME >> Number.
Select the values from B2 to B4 and click one of the buttons to increase or decrease decimal places.
To enter a simple fraction (e.g. 2 3/5), select a cell and enter the value from the keyboard. After you accept, Excel will display it in the same way in which it was entered. If you click this cell, notice that the value is only displayed as a simple fraction, but Excel still remembers it as a decimal fraction.
When Excel treats the fraction as a date
If you enter a simple fraction which doesn’t contain an integer value (e.g. 2/3), and the cell is of a general type, then Excel will recognize this value as date (February 3). If you want this value to be treated as a simple fraction, you need to do one of two things.
- Before you enter this value, format the cell to the fractional type,
- Enter 0 in the decimal place (e.g. 0 2/3).
Excel converts a simple fraction to the lowest denominator
After you enter the number 1 4/8, Excel will automatically convert this value to the form of the smallest denominator (1 1/2). If you want this value to be stored as 1 4/8, go to the number formatting, click the Fraction category and select As eighth (4/8).
Converting simple fractions to decimal fractions
Simple fractions can be converted to decimal fractions as well as decimal fractions to simple fractions. But in both cases, there may be problems with precision.
First, look how to convert a simple fraction to a decimal fraction.
- 1 2/5 will be changed to 1.4. In this case, both fractions are exactly the same.
- Try to convert 2 2/3 to a decimal fraction. If you choose the Number format, then the value will be converted to 2.67. Notice that when you click this value, in the formula bar it will be stored with much higher precision 2.66666666666667. But even this number won’t be exactly the same as (2 2/3).
Converting decimal fractions to simple fractions
The situation gets even more complicated if you try to change a decimal fraction to a simple fraction. Take, for example, the number 1.2345. Go to Format Cells… (Ctrl + Shift + F). In the Number tab, click Fraction. At the top, you will find three positions: with one, two and three digits in the denominator. The more numbers in the denominator, the greater the precision.
Here are some examples:
Experiment with other positions and check which option in a particular situation is best for you.