Sometimes we are required to show numbers as a Fraction in Excel, but it could be litter tough.
In case you try and enter any fraction in a cell, there is a possibility that Excel may change it to a date or text string.
Have you ever think why this happens? This happens because fractions are not one of the data types that Excel understands.
So when we give any fraction numbers to excel it will convert them into a date or text string.
In this short tutorial, I will show you a couple of methods you can use to display numbers as fractions in Microsoft Excel.
Show Decimal as Fractions by changing Excel cell format
Table of Contents
Fractions are essentially decimal numbers. Like 1/2 is 0.5 and 1/4 is 0.25
While fractions may be something that Excel does not understand by default, Decimal is an acceptable data type.
Let’s understand with one excel of the following data set.
Follow Steps :
- Select the Cells that have Decimal Numbers
- Click on Home Tab in Excel Ribbon.
- In Ribbon Go to Number Group and click on Format drop-down.
- Select Fraction from the drop-down.
- The above step will convert decimal numbers into fractions immediately.
Now there are two things we need to know when we are using the inbuilt Fraction format to convert decimals into fractions:
- We will always get a fraction where the denominator of the fraction is a single digit.
- In a case where the decimal cannot be converted into an exact fractional value (while keeping the denominator as a single digit), it will round the number and give you the closest fraction. For example, if you have the decimal 0.9 (which is the decimal value for 9/10), the above method would give you 8/9 (which is an approximate fraction while keeping the denominator to a single digit).
Second inbuilt Fraction Formats in Excel
The default fraction function of excel will give you a result only when a single-digit is available in decimal.
But there is other inbuilt fraction formats available in excel that you can use.
Suppose we have a below data set of decimal numbers.
Follow below-given steps:
- Select Cells in column A.
- Click on Home tab in Ribbon.
- in the Number group, Click on the Dialog box launcher ( titled arrow in the corner). This will open the “Format cell” dialog box.
- In the Format Cells dialog box, within the ‘Number’ tab, click on the ‘Fraction’ category.
- Select any format from the list.
Based on whatever fraction format you choose, the numbers in column A would be displayed in that format.
Below are all the fraction formats that are available to you:
- Up to one digit (1/4)
- Up to two digits (21/25)
- Up to three digits (312/943)
- As halves (1/2)
- As quarters (2/4)
- As eights (4/8)
- As sixteenths (8/16)
- As tenths (3/10)
- As hundreds (30/100)
Creating Your Own Fraction Format in Excel
In most cases, the inbuilt fraction formats are sufficient.
But sometimes, there may be a need where you have to have a specific number as the denominator in the fraction format (say I want the denominator to always be 30).
Since I do not have any inbuilt format where the denominator can be 30, I can create my own fraction format in this case.
- Select the cells that contain Data
- Click the Home tab
- In the Number group, click on the dialog box launcher (that looks like a titled arrow).
- In the Format Cells dialog box that opens up, within the Number tab, click on the Custom category.
- In the Type field, enter the following code: # ??/30
The above format would convert all the decimal numbers into a fraction where the denominator is always 30.
Let me quickly explain how this custom number format works:
- The # at the beginning is needed to show mixed fractions (such as 2 1/30 or 17 12/30). This tells the format that In case the decimal number has an integer portion, show it alongside the fraction. A # represents any number of digits in the format
- ??/30 would force the formatting to always have 30 as the denominator and have space for two digits as the numerator. You can also keep just one ?, but having two question marks would mean that in case you have fractions with one of two digits in the numerator, they would always align in a column (as you can see in the image above).