Do you know how to convert a decimal number to a fraction in Excel? There are two ways using which you can display decimal numbers as fractions. In this blog, we would unlock each of these methods while we move ahead with this tutorial.
Before starting to learn each of these methods, let us do one small activity to understand why one would need to convert a decimal number to a fraction in Excel.
Open a new workbook and type the number 0.25 in cell A1. Now, check the cell formatting of this cell. It is ‘General’ (which means you can use it as a number and perform the mathematical operations of addition, subtraction, division, and multiplication).
Now, let us write the fraction for this number 0.25. Type ¼ (the fraction for 0.25) in cell A2 and press Enter. What did you notice? The excel cell A2 changed its formatting and it converted ¼ into something else (maybe a date).
Also Read: MROUND Function in Excel – Rounding in Excel
Similarly, type =¼ in cell A3 and press Enter. The excel would again return 0.25.
If you put an apostrophe and then type ¼ (‘¼), the excel would store it in a cell as text and not the number. So you cannot use it for doing mathematical operations.
Methods – Convert Decimal to Fraction in Excel
There is an in-built number category for ‘Fractions’ which provides some standard fraction conversions. Additionally using the ‘Custom’ Number formatting to create our own custom fraction format in Excel.
In the coming sections of this blog, we would unlock both of these methods
- Using ‘Fraction’ Category in Format Cells
- Using ‘Custom’ Number Formatting
Let us make sample data ready.
Sample Decimal Numbers
In the below image, you can see that there are decimal numbers which we want to convert into fraction form (numerator/denominator)
Download the sample file for practice using the Download button below:
Where to Find the ‘Fraction’ Category?
The excel has already provided with some in-built formats which can be used to format the cells in Excel. One of the formats is for fractions, which displays the cell value (number) in a fraction form (numerator/denominator).
Also Read: Format Cell Content – Excel ‘Number’ Format
To navigate to this in-built format, use keyboard shortcut Ctrl + 1 or Alt + H + FM.
In the ‘Format Cells’ dialog box that appears, under the tab ‘Number’ you would find a category named ‘Fraction’.
Now, when you know the location of this feature, let us begin with the conversion of numbers into fractions.
Using Format Cell Category – ‘Fraction’
Firstly, select the cells that contain the decimal numbers and open the ‘Format Cells’ dialog box (Ctrl+1).
Select the ‘Fraction’ category and click on OK.
As a result, you would notice that excel converts the numbers in the selected cell into fraction form.
However, if you closely look at the fraction for 6.43, 0.99, and 0.457, you would notice that it is an incorrect fraction. The reason for it is the fraction formatting – ‘Upto One-Digit (¼)’. This means that excel would convert the decimal numbers into fractions but having only one digit in the denominator.
The excel rounded the resultant fraction to the nearest number to 6.43, 0.99, and 0.457.
Therefore, in order to get the exact fraction, select the ‘Upto Two-Digit (21/25)’ or ‘Upto Three-Digit (312/943)’.
Now, let’s move further with the other types of ‘Fraction’ category which are ‘As halves (½)’, ‘As Quarters (2/4)’, and so on.
These types are used to have a fixed base fraction. By fixed base, it means the same denominator for each of the fractions.
Like, if you select the ‘Type’ as ‘As halves (½)’, this means excel would display all the fractions with denominator as 2. Similarly, ‘As quarters (2/4)’ would mean a fixed denominator of 4.
To convert the fraction into a fixed base fraction, excel would return a fraction which is nearest to the normal fraction (but obviously with a fixed base).
Custom Format – Convert Decimal to Fraction in Excel
The ‘Fraction’ category, as learned above, provides standard in-built formats that you can use to convert the decimal numbers into fraction form. However, excel has also provided a feature using which you can create your own format based on your need. To learn more above the number custom formatting, refer to our previous blog on ‘All About Custom Excel Number Format’.
To create your own custom fraction format, select the cells that contain decimal numbers and press Ctrl + 1 to open the ‘Format Cells’ dialog box.
In the ‘Number’ tab, the last category you would find is the ‘Custom’. In this category, under the ‘Type’ input box, you can enter the format of your choice.
To write a fraction form of a number, use the below codes as per your need:
To show the proper fraction for a number with the remainder up to one digit, use format – # #/#.
Likewise, type # ##/## format if you want a proper fraction for a number with remainder up to two digits and # ###/### for remainder up to 4 digits and so on.
The above works similarly like ‘Upto One-Digit (½)’, ‘Upto Two-Digit (21/25)’, etc. options available under the ‘Fraction’ category.
To write an improper fraction (where Denominator is less than or equal to numerator), enter the following format – ###/### [It is not possible to create an improper fraction using the in-built excel fraction formats].
An example image for improper fractions:
Similarly, to create a fraction with a fixed base (apart from the fixed base fraction formats available in the ‘Fraction’ category), use the custom format as # #/5 (where the denominator represents a fixed base of 5, you can change it as per your need).
This brings us to the end of this blog. Share your views and comments in the comment section below.