Write, Display and Convert Fraction in Excel: 7 Methods

Method 1 – Using Mixed Fraction Technique to Display Fraction

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:

Method 2 – Applying Apostrophe Character to Display Fraction

Select cell D5. Type an apostrophe character (‘) before the fraction.
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.
Repeat the above steps for the remaining cells.

Method 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

Select all the decimal numbers that you want to change. We selected Cell D5:D11.
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.

Excel will change all the selected cells to fraction numbers.

3.2 Using Format Cells Window

Select the cells that you want to convert into fractions. We selected cell D5:D11.
Go to the Home tab. Click on the Number Format dialog box launcher.

Open the Format Cells dialog box. Select Fraction from the Category.
Choose your fraction type from the Type. We chose Up to two digits (21/25).
Press OK.

Excel will display the fraction’s numerator and denominator with up to two digits each.

Method 4 – Using Custom Fraction Format to Display Fraction

Apply a Custom format to display fractions in the worksheet without converting them into a Date format.

Apply the custom format for fractions, first, select the cells that you want to convert into fractions. We selected cell D5:D11.
Go to the Home tab. Click the Number Format dialog box launcher.

Open the Format Cells dialog box. Select Custom from the Category.
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.

Get the output as expected.

Method 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.

Select the range of cells you want to change. We selected Cell D5:D11.
Go to the Home tab. Click on the Number Format drop-down menu and select Text from the list.

Click the image for a detailed view.

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

Method 6 – Using TEXT Function to Display Fraction

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.

Select a cell where you want to apply the formula.
Enter the formula below-

=TEXT(C5,"# ?/?")

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

Method 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.

Go to the Insert tab. Click the Symbols drop-down menu and select Equation.

It will take you to the Equation tab. In the Equation tab, click the Fraction drop-down list and select a fraction type 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.
Get the fraction as expected.

Repeat the above steps for the remaining cells.

Method 8 – Using Symbol Tool to Display Fraction

In this example, I’ll use Excel’s built-in Symbol tool to insert fractions, but this tool only supports a limited variety of fractions.

Select a cell such as a cell D5 where you want to insert a fraction.
Click as follows: Insert => Symbols => Symbol.

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

Insert the fraction into the cell. Press Enter.

$Inserting fraction$

Do the same for other cells D6:D10.
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. We’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).
Enter the following formula:

=C5/365.25+1900

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

This will convert all the dates into the decimals.
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.

$Output after converting date into fraction$

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.

Fraction in Excel: Knowledge Hub

<< Go Back to Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!