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.

2. Click on the Format menu, select Number, then Custom number format.

3. 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 sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getActiveRange();

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

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Fraction Format")
    .addItem('Fraction', 'createFraction')
    .addToUi();
}

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.

Have you needed to use fractions in your Google Sheets? Did you find this helpful? Let me know in the comments section!