To format numbers as fractions in Google Sheets, we can use the forward slash in the custom number format.

In Google Sheets, directly entering a number as fraction may convert that number to date if it’s a regular fraction number or to text string if it’s mixed fraction number.

Example Screenshot:

To avoid this, enter the numbers as decimal numbers and then format that numbers as fractions in Google Sheets.

In Excel, there is a specific section in the Format Cell (Ctrl+1) named as Fraction, but it’s not available in Google Sheets.

Google Sheets users need to use the “Custom number format” to display numbers as actual fractions.

In this tutorial, you can get different custom number formats to format numbers as fractions in Google Sheets.

Two Methods to Format Numbers as Fractions in Google Sheets

You can format a number to a fraction in two ways.

• Using the custom number formatting (menu option).
• Using the Text function.

While the former retains the number format, the latter converts the number to a text string.

Using the Number Formatting

Select the cell or cells that contain the numbers that you want to format as fractions. Then access the “Custom number format”.

How can I access the custom number format?

Normally, you can go to the menu Format and click Number and then More formats. There you can see the “Custom number format” as below.

To quickly access this menu, go to the Help menu and in the search field just type the first few characters like “custom” and select “Custom number format” from the suggestion.

You can use the below formats in the given field there.

Regular fractions: _# ??/??

For mixed fractions, please remove the underscore.

One digit –# ?/?

Two digits – # ??/??

Three digits – # ???/???

Halves: # ?/2

Quarters:# ?/4

Eighths: # ?/8

Sixteenths: # ??/16

Tenths: # ?/10

Hundredths: # ??/100

Using the Text Function

Here are a few examples on how to format numbers as fractions using the Text function in Google Sheets.

Suppose cell A1 contains the number 100.50. The below formula would convert that number to a fraction but in text format.

Formula:

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

Output:

100 1/2

Here is real life use of formatting numbers to fraction using the Text function in Google Sheets.

Real-life Use of Formatting Numbers as Fractions in Google Sheets

I have a measurement sheet that contains the measurement in length, width and height in three columns as below. Please see to the columns A, B, and C.

I want the result as per the values in column E. I will show you how to use the Text function to format numbers to fractions and combine them.

Actually I have used the following ArrayFormula in cell E3 which expands to down.

=ArrayFormula(trim(text(A3:A5,"# ??/??")&" x "&text(B3:B5,"# ??/??")&" x "&text(C3:C5,"# ??/??")))

Let me explain how to code this simple formula that combines fractions in Google Sheets.

Step 1:

=text(A3,"# ??/??")

Enter the above formula in cell E3 and drag to the right.

That will place the below formulas in Step 2 and 3 in cell F3 and G3 respectively.

Step 2:

=text(B3,"# ??/??")

Step 3:

=text(C3,"# ??/??")

Combine these formulas using the ampersand in cell E3. Don’t forget to use the “ x “ character as the separator.

=text(A3,"# ??/??")&" x "&text(B3,"# ??/??")&" x "&text(C3,"# ??/??")

Now you can remove the formulas in cell F3 and G3. Change the cell references A3, B3, and C3 to A3:A5, B3:B5 and C3:C5 respectively in the formula. Also, wrap the formula with the ArrayFormula.

=ArrayFormula(text(A3:A5,"# ??/??")&" x "&text(B3:B5,"# ??/??")&" x "&text(C3:C5,"# ??/??"))

To remove the extra space, you can use the Trim function. That’s my final formula.

That’s all about how to format numbers as fractions in Google Sheets. Enjoy!

Resources:

• How to Format Time to Millisecond Format in Google Sheets.
• How to Convert Military Time in Google Sheets.
• Format Numbers as Currency Using Formulas in Google Sheets.
• How to Format Date, Time, and Number in Google Sheets Query.
• How to Format Data to Make Charts in Google Sheets.