Google Sheets is a smart tool that will constantly try to understand the data you insert into it. While it is beneficial and almost always correct in understanding what you want, sometimes it gets ahead of itself and completely misses your point, making things harder and more complicated instead.
An example of that is typing fractions directly into the spreadsheet; typing 1/2 into a cell is automatically registered as a date and not a “half.”
And if you try to type a mixed fraction (for example, 5 1/2), it will be registered as a text string instead. This can make further calculations messy and inaccurate.
In this article, we will discover together two simple ways how to fix this problem, how to get fixed denominators, and other helpful tips.
Converting Decimals to Fractions by using The Custom Number Format.
The first method is done using the Google Sheets menu. This method is recommended if you want to use your fractions in calculations like adding, subtracting, etc.
If your values are already in decimals, skip this point and head over to the next. However, if your values are in fraction form, and keep getting registered as dates, type an equal sign (=) before typing your value (ex: =1/2 instead of 1/2).
This will automatically convert your fractions into decimals and allow you to perform the following method.
Start by selecting the cells which contain decimal numbers, and click the Format menu, Number, then head to the bottom of the menu and click Custom number Format.
In the dialogue box that pops up, type the following formula:
#?/?
Press Apply and you will see all your decimal numbers turn to fractions as follows:
Select more cells (empty or full) and apply this method to automatically convert decimals to fractions as you type them.
Converting Decimals to Fractions by using The TEXT Function
Another method to convert decimals to fractions is by using the Google Sheets TEXT Functions.
One difference between both methods is that using the TEXT Function registers the data as text and allows you to add it to other numbers and texts, unlike the first method, which is more useful in cross-cell calculations.
To do this, fill a column with your data, and head to another column for the results (I’ll be using column A for my data and column B for my results).
Select a blank cell in the Results column and type =TEXT to start your formula. Assign the target cell (A2) then type a comma as a separator. Now type the fraction format as “#?/?”.
The formula should look like this:
=TEXT(A2, “#?/?”)
Press Enter or the Return key to apply the formula. Drag down from the cell to apply the formula to the rest of the cells containing your data. Your spreadsheet should now look like this:
Alternative Custom Number Formats.
It is beneficial to know your way around the main part of the formula if you want more customization with your fractions. You could format your function to deliver a better look to your sheet and increase its readability.
This is mainly concerned with the Insignificant Zeros. These are the zeros located before the number to the left of your decimal point (010.00), and the zeros located after the number to the right of your decimal point (010.50).
The main part of the formula is “# ?/?”, so how can we alter and customize it?
To discuss that, we first need to understand the symbols we are dealing with.
- #: the hashtag, or number sign, represents a digit. It does not register nor show insignificant zeros. This means that the number “010” will be shown as “10” without the zero on the left.
- ?: like the hashtag, the question mark represents a digit. It, too, does not register nor show insignificant zeros. However, it will add a space where an insignificant zero was. This means that the number “010” will be shown as “ 10” with a space where the zero was.
- 0: like both the hashtag and question mark, the 0 represents a digit. Unlike the previous symbols, however, the 0 does register and show insignificant zeros. This means that the number “010” will be shown as “010” and exactly as it was.
- /: the forward-slash symbol simply represents a fraction and shows it in the spreadsheet.
Now, the different ways these symbols could be used depend on the way you want to view your fractions.
Let us say you want your fractions to show the basic one-digit value. This could be done through the following formats: #/#, ?/?, or 0/0.
For fractions of up to two digits, use these formats:
#/##, ##/##
?/??, ??/??
0/00, 00/00
For fractions of up to three digits, use these formats:
#/###, ###/###
?/???, ???/???
0/000, 000/000
Again, each symbol has a different reaction to registering insignificant zeros, so pay attention to what you need and choose a symbol accordingly.
How to Convert Decimals to Fractions with a Fixed Denominator
Sometimes, when you are handling fractions, you want them to have some sort of common base, maybe you want your fractions to appear as divided by 4 or 10.
This might be critical to calculations, or simply for a clearer view and readability of your sheet. In this case, you need a fixed denominator in your formula code.
To do this, on the same sheet of data we used previously, simply select a blank cell, let it be your Results column again, and type =TEXT, select the function from the drop-down menu and then specify the target cell with the decimal number (A2).
Add a comma to separate the segments, then type “# ?/10”. Press the Return key or Enter. Drag down to apply to all the other cells.
What this does is set the number 10 as a fixed denominator in all of your fractions. Now you will have the following formula:
=TEXT(A2, “# ?/10”)
You should get the following results:
You can change the number 10 in the above formula to any other number you would like to be your fixed denominator.
Some popular denominator choices are common measurements like halves, quarters, eighths, and tenths. You can use them through the following formats:
– # ?/2
– # ?/4
– # ?/8
– # ?/10
FAQ
How to type fractions in Google Sheets.
You can type fractions into Google Sheets by heading over to the Format menu, Numbers, clicking on Custom number Format then type # ?/? into the pop-up dialogue box to show fractions in your spreadsheet.
If the values you want to insert are already in fraction form, type an equal sign before you insert the value, meaning (=3/4) instead of (3/4). This will automatically convert your fraction to decimal form. You can then apply the above method to show your values as fractions.
Addition, Subtraction, Multiplication, and Division of Fractions in Google Sheets.
These mathematical calculations are best performed through the first method of conversion, the Custom number Format method. With all of your values and data converted to fractions, head over to a blank cell and do the following:
-To add two or more cells, type one of the following formulas:
=ADD(value1, value2..)
Insert cell location or write the actual numbers.
OR
=value1 + value2..
Insert cell location or write the actual numbers.
– To subtract two or more cells, type one of the following formulas:
=MINUS(value1, value2..)
Insert cell location or write the actual numbers.
OR
=value1 – value2..
Insert cell location or write the actual numbers.
– To multiply two or more cells type one of the following formulas:
=MULTIPLY(value1, value2..)
Insert cell location or write the actual numbers.
OR
=value1*value2..
Insert cell location or write the actual numbers.
– To divide two or more cells, type one of the following formulas:
=DIVIDE(value1, value2..)
Insert cell location or write the actual numbers.
OR
=value1/value2..
Insert cell location or write the actual numbers.
More conversions.
There are many more conversions that can be accessed through Google Sheets. To convert values into percentages, for example, head over to the Format menu, Number, and click Percent.
You can also use the percentage converter directly from the shortcuts tab. Find the % symbol in the shortcuts tab and click on it.
Find other conversions in the Format Menu.
Conclusion
To conclude everything in this tutorial, we learned how to handle fractions in Google Sheets despite the complicated computing.
We covered two methods of converting decimal numbers into fractions: either the Custom number Format method, for more of a calculative approach, or the TEXT Function method, for more of a text approach.
We also covered the used symbols, understood how each one of them treats the insignificant zero and when to use each one. We covered the correct formula to use for fixed denominators and popular choices of denominators and their formulas.
We also covered extra tips for handling fractions in Google Sheets like typing direct fractions, mathematical calculations of fractions, and other conversions that may come in handy.
We hope this tutorial was helpful, informative, and clear.
