Have you ever needed to convert decimals into fractions in your Google Sheets, but weren’t quite sure how to do it? In this article I’ll show you how to instantly convert decimal numbers to fractions without having to do any special math. You can either use a formula or you can change the cell formatting to automatically convert decimals to fractions.
With both of the methods that I will teach you, only the format that the cell displays is changed. The number / value that is entered into the cell stays exactly the same and is simply displayed differently.
To convert decimals to fractions in Google Sheets, follow these steps:
- Select the cell or cells that contain the decimal numbers that you want to convert to fractions
- Click “More formats” on the top toolbar (The button for this option says “123”)
- Click “Custom number format”
- Type #??/?? in the empty field
- Click “Apply”
After following the steps above the decimal values in your cells will now display as fractions.
This article focuses on converting decimals to fractions, but if you want to do the opposite then click here to learn how to convert fractions to decimals in Google Sheets.
Now let’s go over detailed examples of using two different methods to convert to fractions.
Using the TEXT formula to convert decimals to fractions
If you want you can use a formula to make your decimal values appear in fraction format. To do this we will use the TEXT function, which allows you to specify the format that you want values to appear in.
In this case we will use “#??/??” as the criteria, which tells Google Sheets to display the values as fractions. It’s kind of like saying “Which number divided by which number equals the original number?).
To convert decimals to fractions by using a formula in Google Sheets, follow these steps:
- Type =TEXT( to begin the formula
- Type the address of the cell that contains the number that you want to convert to a fraction
- Type a comma, and then type “#??/??” to specify fraction format
- Press “Enter’ on the keyboard. The final formula will look like this: =TEXT(A2,”#??/??”)
So let’s say that cell A2 contains the number 0.5, and we want to convert this number into a fraction. We will do this by entering the TEXT function below, into cell B2.
=TEXT(A2,”#??/??”)
As you can see in the image above, cell B2 shows the fraction 1/2 which is 0.5 displayed in fraction format.
It’s important to note that the number of question marks you use will control how detailed / accurate of a fraction that the formula will display. In the formula above we could have used the criteria “#?/?” where there is only one? On the top and bottom part of the fraction, since the resulting fraction was 1/2 which only contains one number on the top and bottom.
But to allow the formula to display fractions that contain more than one digit on the top or bottom of the fraction, we need to use additional question marks, like this:
#??/??
#???/???
As you can see in the image below, cell A2 contains the number 0.35, which results in the fraction 7/20. Since the number on the bottom of the fraction (20) contains two digits, we need to make sure that we are using at least two question marks on the top and bottom part of the fraction in the TEXT function criteria. If we had only used one question mark on the top and bottom like this #?/? then the cell would display a result of 1/3 instead of 7/20, because it would round to the nearest fraction with one digit on the top and the bottom, which in this case is not what we want.
Changing the cell format to convert decimals to fractions
If you prefer, you can change the format of the cells to convert your decimals into fractions automatically without having to use a formula.
Let’s say that we have a column of numbers that are not whole numbers and contain decimals. What we want to do is change the cell format so that any and all numbers that contain decimals will display in fraction format.
We are going to use the same criteria that we used in the formula from the previous example (#??/??), but in this case we will use the criteria to modify this cell format in the “Custom number format” menu.
Except in this case, we have numbers in our example that are greater than one, so we have whole numbers combined with decimal numbers, and we need to slightly modify the criteria to make sure that these numbers appear correctly in fraction format.
To do this we will add a space before the first question mark, like this:
# ??/??
By doing this Google Sheets will put a space between the whole numbers and the fractions like we want.
To change the numbers in column A to fraction format, first we will select the cells in column A.
Then click the button on the top toolbar that says “123” (This is the “More formats” menu).
Then click “Custom number format”.
When the “Custom number formats” menu pops up, there will be a blank field at the top, where you can designate the format that you want the cells to display in.
Type #??/?? into the blank field, and then click “Apply”.
As you can see in the image below, after following the steps above the cells in column A Now display in number format. Also notice that the numbers that are greater than one display correctly with a space between the whole number and the fraction (mixed numbers).
Now you know two different ways to convert decimals to fractions in a Google spreadsheet!
I was really happy to finally discover these two methods, because the way that I originally converted decimals to a fraction in a spreadsheet was much more difficult (using the Euclidean algorithm). But you don’t have to worry about that because you know the simple methods.
