How to Easily Format Numbers as Fractions in Google Sheets

Custom Number Formatting: The Game-Changer

Formatting numbers as fractions in Google Sheets is a breeze when you know the right tricks. To get started, select the cell or cells containing the numbers you want to format. Next, access the “Custom number format” option.

$Format numbers as fractions in Google Sheets$

To find the custom number format, you have two options. You can either go to the “Format” menu, click on “Number,” and then select “More formats.” Alternatively, you can simply type “custom” in the Help menu search field and choose “Custom number format” from the suggestions.

Once you’ve accessed the custom number format, you can use a variety of formats to display fractions in Google Sheets. For regular fractions, use the format _# ??/??. To display mixed fractions, simply remove the underscore. There are also formats available for one, two, and three-digit fractions, as well as halves, quarters, eighths, sixteenths, tenths, and hundredths.

Using the Text Function: A Nifty Shortcut

Another handy way to format numbers as fractions in Google Sheets is by using the Text function. Let’s say you have a cell (A1) with the number 100.50. To convert this number to a fraction in text format, you can use the following formula:

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

This formula will display the result as “100 1/2,” for example. It can be particularly useful when dealing with measurements or other real-life scenarios.

Real-Life Application: Combining Fractions in Google Sheets

Imagine you have a measurement sheet with columns for length, width, and height. You want to combine these measurements into a single cell, displaying them in fraction format. Here’s how you can effortlessly achieve this:

=ARRAYFORMULA(TRIM(TEXT(A3:A5,"# ??/??")&" x "&TEXT(B3:B5,"# ??/??")&" x "&TEXT(C3:C5,"# ??/??")))

By using the Text function alongside the ArrayFormula, you can easily combine fractions in Google Sheets. This formula will give you the desired result.

That’s it! Now you have the power to format numbers as fractions in Google Sheets. Start adding a touch of elegance to your spreadsheets today!

To explore more resources on formatting data in Google Sheets, check out the following articles: