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.

Using Mixed Fraction Technique

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

Using Mixed Fraction Technique in Excel

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

Output After Using Mixed Fraction Technique


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.

Using Apostrophe Character


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.
Using Excel Ribbon

You can click the image for a detailed view.

  • Excel will change all the selected cells to fraction numbers.

Using Excel Ribbon


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.

Using Format Cells Window

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

Using Format Cells Window

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

Output


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.

Applying Custom Fraction Format

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

Applying Custom Fraction Format in Excel

  • Get the output as expected.

Output after Applying Custom Fraction Format


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.
Applying Text Format

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.

Applying Text 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.

Using TEXT Function


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.

Using Equation Tool

  • 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.
Using Equation Tool

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.

Using Equation Tool

  • Repeat the above steps for the remaining cells.

Output after Using Equation Tool


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.

Using Symbol Tool to display Fraction in Excel

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

Choosing Symbol

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

Output after using symbol tool


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.

Converting Date into decimal

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

Selecting Fraction format

  • 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!
Sishir Roy
Sishir Roy

Sishir Roy, a recent graduate in Civil Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. As an Excel and VBA Content Developer, he has authored 50+ articles, updated 100+, and solved complex Excel VBA challenges. Excelling in troubleshooting and simplifying problems, his love for diverse problem-solving and aiding others is evident in his keen interests in Data Analysis, Advanced Excel, VBA Macro, and Excel Power Query, enriching the project's... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo