ThisCalculator

Everyday

Decimal to Fraction in Google Sheets: 2 Ways

Updated · Standard formula, shown below

Decimal to Fraction in Google Sheets: 2 Ways

To convert a decimal to a fraction in Google Sheets, you can use the TEXT function to format the decimal as a fraction string, or use the QUOTIENT and MOD functions to build a mixed-number fraction manually. The TEXT method is fastest for display, while the manual method gives you separate numbers for the whole part, numerator, and denominator that you can use in further calculations.

Formula

For the TEXT method, the formula is:

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

This tells Sheets to display the value in cell A1 as a fraction with up to one digit in the numerator and denominator. You can change the format to "# ??/??" for up to two digits, or "# ???/???" for up to three digits. The # symbol shows the whole-number part (if any), and the question marks reserve space for the fraction digits.

For the manual method, if your decimal is in cell A1:

=QUOTIENT(A1, 1)          // whole part
=MOD(A1, 1)                // fractional part as a decimal
=TEXT(MOD(A1,1), "?/??")  // fraction string

But to get the actual numerator and denominator as numbers, you need a more complex approach using SUBSTITUTE and INDEX after splitting the text, or you can use a custom formula with REGEXEXTRACT.

Worked Example

Suppose cell A1 contains the decimal 3.75.

  1. In cell B1, enter =TEXT(A1, "# ?/?"). Sheets returns 3 3/4.
  2. To get the numerator and denominator separately, use =REGEXEXTRACT(TEXT(A1, "# ?/?"), "(\d+)/(\d+)") which returns 3 and 4 in two adjacent cells (if you use the formula in two cells, or use INDEX to pull each).
  3. For the whole part, use =QUOTIENT(A1,1) which returns 3.

So the decimal 3.75 becomes the mixed number 3 3/4, which is the same as the improper fraction 15/4.

How the TEXT Function Works

The TEXT function converts a number to text using a format pattern. For fractions, the pattern uses # for the integer part and question marks ? for the fraction digits. The number of question marks controls the precision: one question mark gives halves and quarters, two gives up to 99ths, three gives up to 999ths. Sheets automatically simplifies the fraction to the lowest terms possible within that precision. For example, 0.333 with "# ?/?" becomes 1/3, but with "# ??/??" it becomes 33/100 (since 1/3 can’t be expressed exactly with two digits, Sheets shows the closest approximation).

When to Use Each Method

Use the TEXT method when you only need to display the fraction for readability, such as in a report or a chart label. Use the manual method when you need the numerator and denominator as separate numeric values for further calculations, like adding fractions or comparing ratios. The manual method is more work but gives you real numbers, not text.

Common Mistakes and Pitfalls

  • Using the wrong format code: "# ?/?" limits to one digit in the fraction, so 0.666 becomes 2/3 (correct) but 0.123 becomes 1/8 (approximation). For more precision, use more question marks.
  • Forgetting that TEXT returns text: You cannot use the result in arithmetic operations directly. If you need to calculate with the fraction, use the manual method or keep the original decimal.
  • Not accounting for negative numbers: The TEXT function works with negatives, but the fraction part may show a negative sign. For example, -1.25 becomes “-1 1/4”. This is usually fine, but be aware when parsing.
  • Using QUOTIENT with negative numbers: QUOTIENT(-3.75,1) returns -3, but MOD(-3.75,1) returns 0.25 (not -0.75). This can cause confusion if you try to combine them. Use ABS or handle signs carefully.

How to Build a Full Fraction Converter

If you want a reusable converter that gives you the numerator and denominator as numbers, you can use a custom formula with REGEXEXTRACT and INDEX:

=INDEX(REGEXEXTRACT(TEXT(A1, "# ??/??"), "(\d+)/(\d+)"), 1)

This returns the numerator. Change the INDEX second argument to 2 for the denominator. To get the whole part, use =QUOTIENT(A1,1). If you want the improper fraction numerator (e.g., 15 for 3 3/4), multiply the whole part by the denominator and add the numerator: =QUOTIENT(A1,1)*denominator + numerator.

Limitations and Alternatives

The TEXT method is limited by the number of question marks you specify. It cannot produce exact fractions for repeating decimals like 0.333… unless you use enough digits, but even then it will show an approximation. For exact conversion, you would need a script or a more complex algorithm. A simpler alternative is to use the FRACTION format in Google Sheets’ menu: select the cell, go to Format > Number > Custom number format, and enter # ?/?. This displays the fraction without using a formula, but it still doesn’t give you the numerator and denominator as separate numbers.

Frequently Asked Questions

Can I convert a decimal to a fraction without a formula?

Yes. Select the cell(s), go to Format > Number > Custom number format, and type # ?/? (or # ??/?? for more precision). The display changes to a fraction, but the underlying value remains the decimal.

How do I get the fraction as an improper fraction like 15/4?

Use the TEXT function with a format that omits the whole part: =TEXT(A1, "?/??"). This will show the fraction as an improper fraction, but it may be simplified. For 3.75, it returns 15/4.

Why does 0.333 become 1/3 in one cell but 33/100 in another?

Because the format code determines the precision. "# ?/?" only allows one digit in the numerator and denominator, so Sheets picks the closest fraction with one-digit parts, which is 1/3. "# ??/??" allows two digits, so it picks 33/100, which is closer to 0.333 but not exact.

Can I use the fraction result in other calculations?

No, because the TEXT function returns text, not a number. If you need to calculate with the fraction, keep the original decimal or use the manual method to extract the numerator and denominator as numbers.

How do I convert a decimal to a fraction in Google Sheets for a whole column?

Apply the TEXT formula to the first cell and drag the fill handle down, or use an array formula like =ARRAYFORMULA(TEXT(A1:A10, "# ?/?")).

Does this work for negative decimals?

Yes, the TEXT function handles negatives, but the fraction part will also be negative. For example, -1.25 becomes “-1 1/4”. If you need the fraction part positive, use ABS on the original value before formatting.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.