How to Write a Fraction in Excel (4 Methods)

Fraction is not a default format of Microsoft Excel. When we input any fraction number directly in Excel, it converts to a date (custom) or a text string (general). So writing fraction in Excel is somewhat tricky. In this article, we will discuss 4 ways of how to write a fraction in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Methods to Write a Fraction in Excel

Now, we will show 4 different methods to write a fraction in Excel. We have taken a dataset that consists of products with their amount (Weight) in Kg.


1. Using Fraction Format

This method will change the cell value format to write fractions properly. Follow the steps below.

Steps:

  • Add a column named Fraction Value. Copy the data of the Amount column here.
  • Now, select the cells of the Fraction Value column.
  • Go to the Number group of the Home tab.
  • See an arrow sign at the right bottom side of the Number group. Click on that arrow.

Using Fraction Format of Excel

  • The Format Cells window appears.
  • Click on the Fraction option from the Number tab.
  • After that, choose the required Type.

Using Fraction Format of Excel

  • Finally, press on OK and look at the worksheet.

We can see that values are shown in fraction format.

You can also do this in the following way.

  • First, select all cells of the Fraction Value column.
  • Click on the Number Format section of the Number group.
  • We will get a list of Number formats. Choose the Fraction from the list.

Using Fraction Format of Excel

  • Have a look at the worksheet.

Values are shown in fraction form.


2. Creating a Custom Format to Write Fraction

In this method, we will write data and make a custom format to view them in fraction format. Here, we want to make view our data as a fraction of 30. See the process now.

Steps:

  • Select all the cells of the Fraction Value column.
  • Then, press Ctrl+1 for Format Cells.

  • Choose the Custom option from the Number tab.
  • Put our desired format # ???/30” on the Type box.

Custom Fraction Format in Excel

  • Finally, press the OK button.

Selected data is shown as a fraction of 30 here.


Similar Readings


3. Use Excel TEXT Function to Write a Fraction

The TEXT function converts a value to text to a specific number format.

Here, we will use the TEXT function. This TEXT function will show data in a specified format chosen by the user.

Steps:

  • Go to Cell D4.
  • Write the following formula based on the TEXT function.
  =TEXT(C4,"# ?/?")

Use TEXT Function to Write a Fraction

  • Press the Enter button to execute the formula.

  • Drag the Fill Handle icon.

Use TEXT Function to Write a Fraction

Now, the rest of the data are presented in the same format.


4. Write Fractions with an Apostrophe in the Front

In this section, we will write fractions without using any formula. If we enter any fractional value directly into any cell of the Excel, it turns into a date. To avoid this, we will use an Apostrophe to solve this issue. First, enter the Apostrophe and insert the fraction number.

Steps:

  • First, enter Cell B2 and put an Apostrophe () sign. Then, put a fraction number.

Writing Fraction in Excel

  • Now, hit the Enter button.

Another option is to change the number format before entering any fractional number

  • Go to Cell B3.
  • Click on the Number format of the Home tab.
  • Choose the Text option.
  • Now, enter a fractional value and press the Enter button.

Writing Fraction in Excel

  • See, the fractional number is showing.

See, the fractional number is showing.


Things to Remember

  • You cannot use the fractions produced by methods 3 and 4 for mathematical calculation unless you extract the values by other techniques.

Conclusion

This article described how to write fraction values in Excel. We showed methods based on formulas and cell formates. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo