Convert Decimal to Fraction in Excel
When working with Excel, we come across different data types like numbers, decimals, dates, currency, percentages, fractions, etc. Some of the data types are accepted in Excel by default. However, the values are automatically converted by Excel for some specific data types after we enter them into cells within the worksheet. Fraction is one such type or format that is not supported in Excel by default.
Excel provides easy-to-use methods to set any particular cell to any format, such as fractions. To use fractions in Excel, we must manually set the specific cell type or format as a fraction. Once the cell is formatted as fractions, the value recorded in it does not change automatically. In this way, we can convert decimals to fractions in Excel.
Why do we need to convert decimals to fractions in Excel?
The fraction helps provide a better and clearer view of relationships between quantities compared to the decimal. In particular, Fractions help simplify the proportions by combining two integers into a ratio. Besides, the decimal represents the same quantity in the form of a single number. Thus, representing decimals as fractions seem more pleasant to the eyes.
For example, the fraction 1/3 looks more pleasing and accurate than the decimal number like 0.333333…, and so on. Therefore, when using Excel, it would be better to use fractions in the place of decimals to represent quantities within the sheet. This will help us quickly understand the corresponding quantities and further take decisions accordingly.
Generally, we prefer to use fractions than decimals when representing values such as days, ingredient quantities, chemical compounds, groceries, discrete concepts, etc. In such situations, the fractions provide a better representation, as in the form of ration relationships.
How to convert decimals to fractions using Excel?
As said above, Excel does not support fractions within the cells by default. If we enter any fraction like 1/2, it automatically gets converted as a date or decimals. However, there may be times when we strictly need to use exact fractions within Excel cells. Under such circumstances, we have to convert specific cells format into a fraction.
There are several ways to convert a decimal to a fraction in Excel. We generally use Excel's Fraction Format to convert, display or type numbers as fractions. Depending on the different format or representation of the given number, we can convert decimal to fraction using the following methods:
Convert a Single Number or Cell to a Fraction
When we need to convert any single number or cell into a fraction, we need to follow the steps discussed below:
Convert Multiple Cells to a Fraction
Excel allows converting multiple cells of decimals to fractions at once. We can convert or format certain cells, an entire row or column, into fractions. All we have to do is select all the effective cells in the worksheet and select the fraction option as in the previous method, such as:
Once the cells are selected in the worksheet, we must go to the Home tab > Number Formatting Drop-Down > Fraction.
Convert to more Number Fraction Formats (Up to 2 digits, 3 digits, etc.)
By default, Excel's fraction format displays only up to one digit in the denominator. However, there may be cases when the single-digit fraction may not work as desired. For example, if we type the decimal value 4.9 in an Excel cell and convert it to a fraction using the Home tab > Number Formatting Drop-Down > Fraction, it does not change to a fraction. Instead, it changes to 5.
The one-digit denominator fraction is the main reason why the decimal value 4.9 does not get changed into a fraction. The exact fraction value of 4.9 is 4 9/10, where the denominator consists of 2 digits. Since the default fraction format supports up to one digit denominator by default, Excel typically rounds off the fraction to 5.
To convert decimals into fractions in such conditions, we must set the fraction to have more than one digit in its denominator. For this, we must take advantage of the Format Cells dialogue box by following the steps discussed below:
In this way, we can convert a decimal to a fraction and choose between representing a fraction with a desired number of denominators. For example, when representing a fraction like 9/15 in an Excel cell, we can use the fraction with 'Up to two digits'. When representing a fraction like 320, we can select the fraction with 'Up to three digits'.
Additionally, the Format Cells dialogue box also enables us to represent the desired fraction in halves, quarters, eights, sixteenths, and so on.
Represent as Halves, Quarters, Eights, Tenths, and so on.
By default, Excel auto-adjusts fractions to make the denominators smaller. For example, if we set a cell type as 'Fraction' and enter any number like 2/4, Excel automatically changes it to 1/2. The denominator is reduced from 4 to 2.
Therefore, if we want to prevent Excel from changing the denominator, we must define the denominator using the Format Cells dialogue box. In our example, since we want to fix the denominator as 4, we need to choose the 'As quarters (2/4)' option from the list of fraction formats.
Similarly, if we want to set the denominator to 8, 16, 10, or 100, we can choose other respective fraction formats from the list.
Using a Custom Number Format for Fractions
We can use the Custom Number Format to set any custom fraction format for the desired cells. For example, when we want to fix the denominator to any value other than 2, 4, 8, 10, and 100, we can select the Custom option from the Format Cells dialogue box and specify or enter the desired fraction format code under the Type box.
Suppose we want to convert any decimal value into a fraction where the denominator is fixed to 30. Since there is no option for 'thirtieths', we must use a custom format. We need to enter the format code as (# ??/30) without parentheses inside the type box.
Important Points to Remember
Next TopicFormat Cells in Excel