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:

• We need to click and select the particular cell in the worksheet.
  
• We must go to the Home Menu tab and click the drop-down icon next to Number formatting, as displayed below:
  
• From the drop-down list, we must click on the Fraction
  
  This will convert the selected cell or its decimal number to a corresponding fraction.
  

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:

• To select multiple contiguous cells in the worksheet, we can click-and-drag using the mouse or click on the first and last effective cell while holding down the Shift key on the keyboard.
• To select multiple non-contiguous cells in the worksheet, we can click on each cell individually while holding down the Ctrl key on the keyboard.
• To select the entire row or a column, we can click on the respective row header of the column header.

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:

• First, we must select the particular cell(s) we want to convert to fractions.
• Next, we need to press a right-click button on the selected cells and choose the ‘Format Cells‘ option. This will launch the Format Cells window.
  
  Alternately, we can click on the ‘More’ icon from the Number formatting group under the Home tab to open the Format Cells dialogue box.
  
• In the Format Cells window, we need to select the ‘Fraction‘ under the category list, and we will get another list of different fraction formats in the right-side section. It looks like this:
  
• We can select the desired option to decide how we want to display the fraction value based on its denominator. We can select to display our fraction value as a single-digit denominator or a fraction with a double-digit or triple-digit denominator.
  In our example, the fraction value has a denominator with two digits. Therefore, we choose the ‘Up to two digits‘ option from the right-side section.
  
• Lastly, we must click the OK As soon as we click the OK button, our decimal value (4.9) gets converted into a fraction (4 9/10) instead of the whole number (5).
  

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

• The keyboard shortcut “Ctrl + 1” helps us instantly open the Format Cells dialogue box.
• When we need to display a fraction with two or more digits in a denominator, we must use the Format Cells dialogue box.
• We can also create a custom fraction format using the Custom Number Format.