Convert Decimals to Fractions in Google Sheets (2 Easy Ways)

Google Sheets is a smart spreadsheet tool that has been made to be user intuitive. This means that sometimes when you enter data in Google Sheets, it will try to understand what you mean and help you with it.

But sometimes, that’s not what you want.

One such case is when you try to enter fractions in Google Sheets and it converts these fractions into dates.

Or if you try and enter a mixed fraction that has a whole number and the fraction, it would interpret it as a text value.

And sometimes you may have a decimal value that you want to convert to a fraction, and you can’t because Google Sheets does not interpret what you enter as a fraction.

Thankfully, there’s a really simple workaround to this (which is already built-in in Google Sheets)

In this tutorial, I will show you two really simple ways to convert decimal values into fractions in Google Sheets (using custom number formatting and formulas)

So let’s get started!

Convert Decimals to Fractions Using Custom Number Formatting

With Custom Number Formatting, you can change how your value appears in the cell (without changing the actual value in the center).

So if you have a decimal value, you can format it in such a way that it shows up as a fraction. and at the same time, you can also use it in calculations (just like you would have used the decimal number).

Suppose you have the below decimal numbers in a column in Google Sheets and you want to show all of these as fractions.

Below are the steps to do this:

1. Select the cells that have the decimal values that you want to convert to fractions
2. Click on the Format option in the menu
3. In the options that show up, hover the cursor over the Number option
4. In additional options that show up, Go to More Formats and then click on ‘Custom number format’
5. In the Custom number format dialog box that opens up, enter the following format:

   # ?/?

   

6. Click on Apply

Before link steps would change the format of the cell so that the decimal numbers now show up as fractions.

Useful Custom Number Formatting Symbols for Google Sheets Fractions

The above Google Sheets fraction format has three different symbols:

• # – it represents a digit in the number, and would not show the insignificant 0. For example, if a number in the cell is 10 and the format is ###, it would still show 10 and not 010 (as the first 0 is insignificant)
• / – a forward slash is used to show fractions
• ? – just like hash, a ? also represents a digit in the number and would also not show the insignificant 0, but the insignificant 0 will appear as space

The upper format makes sure that in case there is a decimal number, it follows the fraction format and shows the whole number and the fraction part (where the fractions would only consist of single digits)

In case you want the fractions to have two digits in the numerator and the denominator, you can use the below format:

# ??/??

If you’re still confused about the difference between #, 0, and ? and how to use these for fractions, below is a table where I have shown the result of all the three formats (where the formats used is mentioned in the yellow cells)

Convert Decimals to Fractions in Googlre Sheets Using TEXT function

You can also use the TEXT formula to convert your decimal number into a fraction by specifying the format in which you want that fraction.

This works in a similar way as the custom number formatting method covered above, with one difference – the result of the formula is a text value.

One benefit of using this method over the custom number formatting method is that you can combine the result of the text formula with other text/numbers.

Suppose you have the below decimal numbers in a column in Google Sheets and you want to show all of these as fractions.

Below is the formula that will give you the resulting fraction (as text):

=TEXT(A2,"# ??/??")

The TEXT formula takes 2 arguments:

• the reference of the cell that has the value that you want to convert into a fraction
• The format that you want to apply to the result

If you need to change the format of the cells later, you can simply edit the second argument of the TEXT function

Converting Decimals to Fractions with a Fixed Denominator

In some cases, you may want to convert a number into a decimal value but have a fixed denominator.

For example, you may always want the denominator to be 5 or 10. This can easily be done by changing the format code.

Suppose you have a dataset as shown below and you want to convert these decimals to fractions while having 10 as the denominator in the fraction.

Below is the formula that will do this:

=TEXT(A2,"# ?/10")

In the above formula, I have specified the denominator of the fraction as 10. Why converting a number from decimal to fraction, Google Sheets will make sure that it complies with the denominator that I have specified.

So 5.1 would become 5 1/10 and 5.2 would become 5 2/10.

In case you want to use any other denominator, specify that instead of 10 in the above formula.

If you want to use the Custom number format method instead of the formula, use the same format used in the formula and put it in the ‘Custom number format’ dialog box

Here are some popular fraction formats that might come in handy:

• Up to two digits: # ??/??
• Up to three digits: # ???/???
• As halves: # ?/2
• As quarters; # ?/4
• As eighths: # ?/8
• As sixteenth: # ??/16
• As tenths: # ?/10
• As hundreds: # ??/10

Google Sheets Decimal to Fraction Sheet FAQ

Can You Type Fractions in Google Sheets? / How Do I Write a Fraction in a Spreadsheet?

Yes you just have to change the number formatting in the cells by:

1. Navigate to Format>Number>More formats>Custom number format
2. Enter # ?/? as the number format to show fractions

How Do You Subtract Fractions in Google Sheets?

The same way you would subtract with any other numbers in Google Sheets:

1. Click an empty cell
2. Type the minus symbol (-)
3. Click or type the larger fraction you wish to subtract from
4. Click or type the smaller fraction
5. Press enter

The answer will show up in the cell you chose

How Do I Convert a Fraction to a Percentage in Google Sheets?

You just have to change the number formatting.

1. Highlight the cells you wish to convert
2. Navigate to Format>Number and click on Percent

You can follow a similar process for Google Sheets to convert a fraction to a decimal. Just click Number instead of percent.

Changing Decimals to Fractions Final Word

So these are some easy ways that you can use to convert decimals to fractions in Google Sheets. Using the different format notations, you can also specify the format of the fraction number that you want.

I hope you found this tutorial useful.

Other Google Sheets tutorials you may also like:

• How to Copy Conditional Formatting in Google Sheets
• Rounding Numbers in Google Sheets
• How to Check Spelling in Google Sheets
• How to Clear Formatting in Google Sheets
• How to Calculate Percentage in Google Sheets
• How to Convert Currency in Google Sheets (Google Finance Function)