Skip to Content

How to Convert Decimals to Fractions in Google Sheets

Google Sheets is a powerful program with many features that need to be learned to be proficient in the program.

One thing that takes beginners a little bit to learn is how to convert decimals to fractions.

Sometimes in your spreadsheet, you might want to show a fraction instead of a decimal for certain values. Other times you might try to manually enter the fractions and Google Sheets might think you are trying to enter a date, which is not what you want.

In this tutorial, I am going to show a few ways to convert decimals to fractions using custom formatting.

Table of Contents

How to Convert Decimals to Fractions

Fractions with Custom Number Formatting

The easiest way to convert decimals to fractions is to use custom number formatting. Here’s how it’s done:

  1. Highlight the cells with the decimals that you want to convert to fractions
  2. In the top menu select Format
  3. Select Number and then select More Formats 
  4. Select
  5. In the text box enter “# ?/? ” and then select the Apply button
  6. This will convert your decimals to fractions

Decimals to Fractions Custom Format

If you want to show more digits (numerators and denominators) in your fraction you can enter this as the custom format: #??/??

Fractions Using the Text Formula

You can also convert decimals to fractions using the TEXT function in Google Sheets. This method works similar to the custom number formatting because you still will need to use # ?/? in the formula to convert to fractions.

Only this time you are using it with the TEXT function. Here’s how it’s done:

  1. Select a cell where you want the fractions to be and type the equals sign (=) to start the formula
  2. Begin typing: “text” and press enter when it appears as an option
  3. Select the first cell with the decimals that you want to be converted and then press the comma (,) button on your keyboard
  4. Inside quotation marks indicate the fraction format (# ?/?)
  5. Close the formula with a closing parenthesis and press Enter on your keyboard
  6. Copy the cell with the formula and paste it down if you have other rows that you  need to be converted

Text Function Fractions

The TEXT function has two arguments: number, format.

So first you select the cell you want to be converted, and then you enter the fraction # ?/? format to indicate that you want the selected cell to convert to a fraction. Again if you want more numerators and denominators in your fraction you would enter more question marks in your fraction format (# ??/??).

How to Convert to Fractions with a Fixed Denominator

There may be situations where you want to convert a decimal to a fraction but have a fixed denominator.

For example, maybe you want all the fractions to have a denominator of 10.

This is possible in Google Sheets by entering that same fraction formatting we have been using (# ?/?) and changing the last question mark to the denominator we want. So instead of using # ?/? you would use # ?/10 if you want a denominator of 10.

Fixed Denominator Fractions

You can change the last denominator to a fixed number using both methods of converting to fractions that we have covered in this tutorial. It works using custom number formatting and using the TEXT function

Closing Thoughts

Those are the easiest ways to convert to fractions in Google Sheets. The key thing to remember is the fraction notation in Google Sheets (# ?/?).

If you are still confused about how to do this, be sure to watch the video and I will walk you through the exact steps.

More Google Sheets Tutorials:
How to Do Basic Math Formulas
How to Convert Formulas to Values
How to Use Not Equal To
How to Round Numbers
How to Calculate Percentage