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 fractions in Excel is somewhat tricky. In this article, we will discuss 4 ways of how 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 to Write a Fraction in Excel
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.
- The Format Cells window appears.
- Click on the Fraction option from the Number tab.
- After that, choose the required Type.
- 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.
- Have a look at the worksheet.
Values are shown in fraction form.
Read More: How to Make Fractions Smaller in Excel
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.
- Finally, press the OK button.
Selected data is shown as a fraction of 30 here.
3. Writing a Fraction Using Excel TEXT Function
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,"# ?/?")
- Press the Enter button to execute the formula.
- Drag the Fill Handle icon.
Now, the rest of the data are presented in the same format.
4. Writing 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.
- 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.
- See, the fractional number is showing.
See, the fractional number is showing.
Read More: [Solved!] Fraction Changing to Date in Excel
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
This article described how to write fraction values in Excel. We showed methods based on formulas and cell formats. I hope this will satisfy your needs. Please give your suggestions in the comment box.
Related Articles
- How to Add a Stacked Fraction in Excel
- Convert Fraction to Decimal in Excel
- How to Format Fraction to Percentage in Excel
<< Go Back to Fraction in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!