Fraction in Excel (Write, Display and Convert)

In this article, you’ll learn everything one should know about fractions in Excel. This article covers how to write fractions, convert them, and format them in different ways. With these techniques, you can work with fractions accurately and make your spreadsheets more effective.

I used Microsoft 365 for this article. However, all of the methods and functions that are covered in this article are available in all Excel versions.

Knowing how to work with fractions in Excel is useful in various fields. It can assist you in a variety of fields, including finance, accounting, engineering, and teaching. You may arrange recipes, make calculations, write reports, and finish academic assignments by formatting and converting fractions. By using Excel’s fraction features, you can handle fractional problems in the real world with simplicity and accuracy.

Click the image for a detailed view.

Download Practice Workbook

How to Display Fraction in Excel?

Here, I’ll cover 7 different ways to display fractions in Excel.

1. Using Mixed Fraction Technique to Display Fraction

In Excel, writing a mixed fraction is simple. Just type the whole number, add a space, and then write the proper fraction with a divide sign. You can also display proper or improper fractions as mixed fractions by inserting 0 as the whole number, followed by a space and the proper fraction with a divide sign.

• In cell D5, we have written a proper fraction in a mixed fraction manner, where 0 represents the whole number.

• After pressing Enter, Excel automatically takes this as a fraction.

• Repeat the above steps for the remaining cells and you will get the output as follows:

2. Applying Apostrophe Character to Display Fraction

You can apply an apostrophe character at the beginning of a fraction. This enables you to store the fraction in Text format and not convert it into Date format. Follow the steps below to write a fraction in Excel in this way.

• First, select cell D5. Type an apostrophe character (‘) before the fraction.
• Then, write the numerator. Type a forward slash (/) to represent the divide sign. Enter the denominator.
• Press Enter, and Excel will treat it as a fraction.
• Now, repeat the above steps for the remaining cells.

3. Applying Fraction Format to Display Fraction

Excel has a built-in format option named Fraction. This helps you to convert any numbers into fractions.

3.1 Using Excel Ribbon

• Firstly, select all the decimal numbers that you want to change. We selected Cell D5:D11.
• Next, go to the Home tab. Click on the Number Format drop-down box and select Fraction from the list.

You can click the image for a detailed view.

• As a result, Excel will change all the selected cells to fraction numbers.

3.2 Using Format Cells Window

• Firstly, select the cells that you want to convert into fractions. Here, I selected cell D5:D11.
• Next, go to the Home tab. Click on the Number Format dialog box launcher.

• It will open the Format Cells dialog box. Select Fraction from the Category.
• Next, choose your fraction type from the Type. I chose Up to two digits (21/25).
• Lastly, press OK.

• As a result, Excel will display the numerator and denominator of the fraction with up to two digits each.

4. Using Custom Fraction Format to Display Fraction

You can also apply Custom format to display fractions in the worksheet, and not convert it into a Date format.

• To apply the custom format for fractions, first, select the cells that you want to convert into fractions. Here, I selected cell D5:D11.
• Next, go to the Home tab. Click on the Number Format dialog box launcher.

• It will open the Format Cells dialog box. Select Custom from the Category.
• Next, choose your fraction type from the Type. I chose the type # ??/??. This means both the numerator and denominator will be displayed up to two digits.
• Press OK.

• Therefore, you will get the output as expected.

5. Applying Text Format to Display Fraction

Another way to display fractions in Excel is to format the cells into the text format before entering fractions.

• Firstly, select the range of cells you want to change. Here, I selected Cell D5:D11.
• Next, go to the Home tab. Then, click on the Number Format drop-down menu and select Text from the list.

Click the image for a detailed view.

• Now, write the numerator, type a forward slash (/) to represent the divide sign, and enter the denominator. It won’t change to the Date format.

6. Using TEXT Function to Display Fraction

By using the TEXT function in Excel, you can display fractions according to your preference. The fractions are displayed in a Text format, not a Date format.

• Firstly, select a cell where you want to apply the formula.
• Next, enter the formula below-

• It will format the value in cell C5 as a fraction with a one-digit numerator and denominator.
• After that, drag the Fill Handle icon downward to apply the formula to the remaining cells.

7. Applying Equation Tool to Display Fraction

We can also insert fractions using the Equation tool. This enables you to insert a fraction in the worksheet, but not within a particular cell. So I have increased the row height to make it fit within a cell.

• For that, go to the Insert tab. Click on the Symbols drop-down menu and select Equation.

• It will take you to the Equation tab. Now, in the Equation tab, click on the Fraction drop-down list and select a fraction type that you want to insert.

Click the image for a detailed view.

• You’ll see a fraction template with blank spaces for the numerator and denominator.
• In the corresponding placeholders, type or enter the desired values for the numerator and denominator.
• As a result, you’ll get the fraction as expected.

• Now, repeat the above steps for the remaining cells.

8. Using Symbol Tool to Display Fraction

In this example, I’ll use Excel’s built-in Symbol tool to insert fractions, but only limited varieties of fractions are available in this tool.

• Firstly, select a cell such as a cell D5 where you want to insert a fraction.
• Next, click as follows: Insert => Symbols => Symbol.

• The Symbol dialog box will appear. Select the appropriate fraction that you want to insert and click on the Insert button.

• This will insert the fraction into the cell. Now press Enter.

• Similarly, do the same for other cells D6:D10.
• Finally, you’ll get the output as below.

How to Convert Date to Fraction in Excel

In this example, I’ll show how to convert a date to a fraction. First I’ll convert the dates into decimals and then I’ll convert the decimals into fractions.

• Select a cell where you want to display the result (for example, cell D5).
• Next, enter the following formula:

• Drag the Fill Handle icon to copy the formula to the remaining cells.

• This will convert all the dates into the decimals.
• Now, select the cells D5:D10.
• Go to the Home tab.
• Click on the Number Format drop-down menu, and select Fraction.

• This will convert all the decimals into fractions.

Frequently Asked Questions

1. Can I display fractions with different denominators in Excel?

Yes, you can display fractions with different denominators in Excel by using the Custom fraction format. This feature allows you to specify the format for fractions, including the numerator and denominator, and thus gives you the flexibility to represent a wide range of fractions with varying denominators.

2. How do I round a fraction to a specific number of decimal places in Excel?

To round a fraction to a certain number of decimal places, you can use the ROUND function in Excel. Put your fraction inside the ROUND function and tell it how many decimal places you want. For example, if you have a fraction in cell A1 and want to round it to two decimal places, use the formula =ROUND(A1, 2). This way, your fraction will be rounded to the precision you need while keeping its fraction form.

Conclusion

In conclusion, Excel offers several methods to display fractions. The mixed fraction technique offers traditional formatting while applying the Fraction format and Custom fraction format are simple options. The TEXT function allows flexibility and works well for complex fractions. The Equation tool is best for mathematical operations, and the apostrophe character offers a quick but basic display. The Text format preserves input as a text, and hence help you to negotiate the problem associated with fraction, but you won’t be able to use it in a mathematical formula. Choose the best way to display fractions according to your needs.

If you have any further questions, feel free to ask in the comment section below or on our forum. Thank you for reading this article!

Fraction in Excel: Knowledge Hub

• Convert Fraction to Decimal
• Add a Stacked Fraction
• Make Fractions Smaller
• Format Fraction to Percentage
• [Solved!] Fraction Changing to Date

<< Go Back to Number Format | Learn Excel