Fractions in Google Sheets

What Are Fractions in Google Sheets?

Fractions in Google Sheets are a way of representing numbers as parts of a whole. Mathematically, a fraction is a numerator value by denominator value. In Google Sheets, the best way to display a fractional value is by formatting the cells in fraction form instead of decimal form. Working with fractions in Google Sheets is useful when dealing with precise measurements, recipes, etc. Here, we look at how to go about inputting fractions.

This article shows us how to input and format fractions in different ways and handle them in Google Sheets. From entering fractions to formatting them accurately, we will look at some handy tips to make them more useful in our spreadsheets. In the example below, we have entered some values 0.75 and 0.5 directly in Google Sheets. We then go to Format -> Number -> Custom Number Format and choose the options # ?/? and # ??/?? to display them in fractional form, as shown below. To prove that Google Sheets has interpreted them as fractions, we added the values in cells A1 and A2 to get 1 1/4.  

How to Use Fractions in Google Sheets?

There are several different ways to enter and use fractions in Google Sheets. Let’s examine them.

There are several different ways to enter and use fractions in Google Sheets. Let’s examine them.

Direct Entry of Fractions: We can type a fraction into a cell directly as text, such as 1/4, 3/5, etc.

Format Cells as Fractions: Select the cells you wish to format. Now, go to the Format option in the menu on top. Choose Number and then Custom Number Format.

Depending on your requirements, you can choose from different formats, such as “Up to one digit,” “As simple fractions,” or a different form.

For instance, if a value of 0.75 is entered in a cell and we format the cell as a fraction, 3/4 is displayed.  

3 Ways to Format Fractions in Google Sheets

Let us show you how to use “Format” to format cells as a fraction. We’ll use an example to show some students’ scores out of 10 in Google Sheets. We have some values in Column A.

#1 – Format Fractions with Conditional Number Format

Let us show you how to use “Format” to format cells as a fraction. We’ll use an example to show the scores of some students out of 10 in Google Sheets. We have some values in Column A.

Step 1: Select the values. Go to the menu at the top and choose Format -> Number.

Choose Custom Number Format.

Step 2: Here, for a fractional display, you can choose either of the options from # ?/? or # ??/??. Here, the # sign is a placeholder to prevent the display of unnecessary leading zeros or zeros after the decimal point,

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

Next, the forward slash ‘/’ means fractions, and the question mark ‘?’ shows that a number is to be displayed. The difference between the two formats is the number of digits that appear in the numerator and denominator.

# ?/? displays a fraction with single-digit numerator and denominator. Hence, 0.4 will be displayed as 2/5, If you opt for # ??/??, you get a two-digit numerator and denominator. Here, 0.4 will display as 20/50.

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 choosing or customising your required format, click on Apply.

Now, all numbers in Column A are formatted as fractions.

#2 – Format Fractions with TEXT Function

Now that we have seen one important way to format numbers as fractions let us look at a few other ways to do it. One of them is using the TEXT function. The TEXT function helps format the values as text using the Custom Format options.

The syntax of the TEXT function is as follows:

=TEXT(number, format)

Here, the number is the number you input, and the format argument is where you can enter the required fraction format. The second argument must be enclosed within quotes.

Again, let’s examine a set of numbers. They are in numerical decimal format, as shown below.

Step 1: To display the numbers in a fractional form, we use the TEXT function as follows:

=TEXT(A1, “# ?/?”). Press Enter.

Step 2: The first number is displayed in fractional form. Now, drag the formula to the other cells to obtain the fractional form for all the other numbers.

Remember, the disadvantage of this method is that the TEXT function in Google Sheets turns them into a string. It is only useful for display purposes and cannot be used in calculations.

#3 – Format Fractions with the QUERY Function

Having seen the disadvantages of the TEXT function, let us look at another way to display fractions in Google Sheets. For this, we may use the QUERY function. The QUERY function is used for creating fractions. When using the QUERY function, the fractions remain as numbers, unlike the TEXT function. Thus, we can perform any mathematical calculation with them.

The syntax of the QUERY function in Google Sheets is as follows:

=QUERY(data, query, [headers])

• data is a range of cells containing the data we will query.
• query – we specify the query’s conditions here in the SQL language.
• [headers] – optional argument to indicate if the first row is a header.

To use the QUERY function to create the fractions, use this syntax.

Let us take some scores of a few students in a sheet for a maximum of 100.

Step 1: Enter the following function in cell B2.

=QUERY(A1:A6, “SELECT A FORMAT A ‘# ?/?'”, 0)

The above function helps format the score column as fractions, which are numbers.

Explanation:

=QUERY(A1:A6, – It shows the range of data we are querying.

SELECT A: This is a part of the SQL language which specifies what column to retrieve. It will retrieve the values in the specified column.

FORMAT A ‘# ?/?’: It helps you apply custom formatting to the results of your query. Here, it applies a fraction format to column A.

0: This means that the data range has no headers.

The format ‘# ?/?’ will display the values in fraction format and as numerical values.

Step 2: Press Enter. It converts all the decimal values to fractions as numbers. So, one can perform calculations in them.

How to Convert Fractions to Percentages?

To convert fractions to decimals in Google Sheets, follow these steps:

Step 1: Select the cell or range of cells containing the fractions you want to format as percentages.

Step 2: Click on the ‘Format’ menu -> Number -> Custom Number Format.

Important Things to Note

1. The FORMAT used in QUERY is used to apply formatting to the values returned by the QUERY function.
2. We mainly use fractions in Google Sheets for analyzing ratios in business, science and statistics.
3. If you need to round fractions, you can use the built-in ROUND, ROUNDUP, or ROUNDDOWN functions.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand Fractions in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Are Fractions in Google Sheets. We learn how to use fractions & how to format them in different ways with a working template. You can learn more from the following articles. –

P-Value in Google Sheets

MAX In Google Sheets

LOWER Google Sheets Function