# 4 Ways to Format Fractions in Google Sheets

Do you want to display fractions in Google Sheets?

When working with numerical data in a spreadsheet, formatting is crucial for presenting the data in a clear and organized manner. While Google Sheets offers a multitude of formatting options for numbers, it lacks a dedicated feature for formatting fractions.

If you want to display decimals as fractions in your Google Sheets, you will have to tweak a few spreadsheet menus and/or functions. However, this process is simple and involves just a few steps and the use of specific functions.

This article will guide you through a step-by-step process for formatting decimals as fractions in Google Sheets.

## Format Fractions with Conditional Number Format

In addition to the formatting options natively provided by Google Sheets, conditional number formatting is a powerful tool that allows you to create customized formats for numbers based on specific conditions, thereby extending the formatting options available to you. With conditional number formatting, there’s no limit to the types of number formats you can create, including formatting fractions.

To use conditional number format to create fractions in Google Sheets, follow these steps.

1. Select the cells containing the decimal numbers you want to format as a fraction.
1. Click on the Format menu, select Number, then Custom number format.
1. Enter either one of these syntaxes into the search box – `# ?/?` or `# ??/??`.
``# ?/? or # ??/??``

From the syntax, the pound sign ‘#’ is a digit placeholder that prevents the display of insignificant zeros such as leading zeros or zeros that appear after a decimal point.

The forward slash ‘/’ is used to show fractions, and the question mark ‘?’ also works like the pound sign. However, it displays any insignificant zeros as a space.

The difference between the `# ?/?` and `# ??/??` syntaxes is in the number of digits that appear in the numerator and denominator. The `# ?/?` displays a fraction with a numerator and denominator each having one digit. That is 0.5 will be displayed as 1/2 and 0.75 will be displayed as 3/4.

The other syntax `# ??/??` will display a fraction with a two-digit numerator and denominator. That is 0.5 will display as 50/100 and 0.75 will display as 75/100.

You can increase the number of digits you want to appear in the numerator and denominator by increasing the number of question mark symbols in the syntax.

After entering the syntax, click on Apply.

Now, all numbers in the Math Score column are formatted as fractions.

## Format Fractions with TEXT Function

The TEXT function helps you to format cell values as text using custom format options.

``=TEXT(number, format)``

The `TEXT` function uses two arguments – `value` and `format`. The `value` argument takes the value, cell or range of cells you want to add a custom format, while the `format` argument is where you enter the custom format syntax. Entries in the `format` argument must be enclosed in quotation marks.

To use the `TEXT` function to create fractions in Google Sheets, use this syntax.

``=TEXT(C3, “# ?/?”)``

Enter this syntax into a blank cell and drag down the formula to duplicate the formula into other cells to create fractions for every value in the Math Score column.

## Format Fractions with the QUERY Function

The `QUERY` function is a powerful Google Sheets function that can do almost anything, including creating fractions. The previous method discussed using the `TEXT` function for creating fractions, however, the `TEXT` function turns the fractions into a string. With the `QUERY` function, your fractions remain as numbers, and you can carry out any mathematical calculation with them.

``=QUERY(data, query, [headers])``

The QUERY function has three parameters but only needs two to carry out its operations.

• `data` – refers to the range of cells that contain the data you want to query.
• `query` – refers to the syntax that specifies the conditions of the query. The `QUERY` function uses the syntax of the SQL language.
• `[headers]` – is an optional argument that specifies whether the first row of the data range should be treated as headers.

To use the `QUERY` function to create the fractions in Google Sheets, use this syntax.

``=QUERY(B2:C7, “SELECT C FORMAT C ‘# ?/?’”, 1)``

When you copy and paste this syntax into an empty cell, the values in the Math Score column will be formatted as fractions. And they’re formatted as numbers unlike when you use the `TEXT` function.

## Format Fractions with a Custom Menu

So far, all solutions involve quite a few steps. By creating a custom menu using an Apps script, you can have a solution to create a fraction in Google Sheets that requires only two clicks.

To begin, go to the Extensions menu and select Apps script to open the apps script editor window.

``````function createFraction(){
var range = sheet.getActiveRange();

range.setNumberFormat("# ?/?");
}

function onOpen() {
}
``````

Copy and paste the syntax into the editor. Click on Save and Run, then grant the necessary permissions.

The above code defines two functions in Google Sheets, `createFraction` and `onOpen`. Both functions work together to create a custom menu item in the spreadsheet UI that allows the user to format selected cells as fractions.

Here’s an explanation of the syntax used in the code:

• `function createFraction(){...}` – This function uses the `setNumberFormat()` method to set the number format of the active range of cells to a fraction format, with one digit each for the numerator and denominator. The “# ?/?” format string is used to specify the format, which displays the fraction with a single-digit numerator and denominator, separated by a slash (/) symbol.
• `function onOpen(){...}` – This function uses the `createMenu()` and `addItem()` methods to create a custom menu item in the spreadsheet UI. The menu item is called Fraction Format and has a sub-item called Fraction, which is associated with the `createFraction()` function. The `addToUi()` method is used to add the menu item to the UI.
• `var sheet = SpreadsheetApp.getActiveSheet()` –This line of code gets a reference to the currently active sheet in the spreadsheet.
• `var range = sheet.getActiveRange() `–This line of code gets a reference to the currently selected range of cells in the active sheet.

When you refresh the spreadsheet, the syntax will create the custom menu. Before using the Fraction option, select the cells you want to format.

## Conclusions

Formatting fractions in Google Sheets can be a useful tool when working with numerical data that contain fractions.

Using the methods outlined in this article, you can quickly and easily format decimal values as fractions using Conditional number formatting, the `TEXT` or `QUERY` function, or the custom apps script menu.

## Oluwaseun Olatoye

Oluwaseun is a business intelligence analyst with expertise in Google Sheets and SQL programming language. He has worked with various businesses to make data-driven decisions. He enjoys helping others learn and grow.

### Subscribe

Subscribe for awesome Google Sheets videos 😃

## Related Articles

#### 3 Ways to Gray Out Unused Cells in Google Sheets

Looking for an easy way to gray out unused cells in your spreadsheet? Well, look no more. Graying out unused cells is...

#### How to Format US Phone Numbers in Google Sheets

Do you need to format phone numbers in Google Sheets? When you format numbers in Google Sheets, you add more clarity...

#### 4 Ways to Show Negative Numbers Inside Parentheses in Google Sheets

Do you want to show minus signs as parentheses? This post is going to show you how to display any negative numbers...