Learning how to convert decimals to fractions in Google Sheets is useful if you want to work with fractions rather than decimals.
Meaning, values presented in Google Sheets shall be expressed as the quotient of two numbers separated by a slash.
Table of Contents
By default, non-integer numbers are expressed as
decimals in most spreadsheet applications like Google Sheets. Most users are fine with working with
decimals, but there may be instances where
fractions are not only preferred but better suited for the problem at hand. Although Google Sheets is a smart program that is capable of providing easy solutions to complex problems, it could get confused on what type of data you are trying to input. This is especially true when trying to input
For example, when you input ½ to a cell, you may be surprised to see that it has been registered as a date rather than a number. Inputting mixed fractions also leads to a similar problem as it will be considered as a text string. Not catching these issues can lead to inaccuracies and errors when making calculations with these values.
Therefore, we need to instruct Google Sheets that we are trying to work with
fractions to solve these problems. In this article, we’ll learn two ways on how to convert
fractions in Google Sheets.
Consider this example.
As an aspiring baker, you scour the internet for a delicious red velvet cake recipe. You stumble upon this blog post with tons of satisfied reviews from those that recreated the recipe. However, upon inspection of the ingredient list, you see that most are measured in metric units.
You don’t have a kitchen scale or a small enough container to measure up to an accuracy of 10mL. Since you only have cups and tablespoons as measuring devices, you decide to determine their equivalent measurements with these units. You look up the corresponding conversion factor for each ingredient (because yes, its different for each one) and end up with the following results:
Although this takes you one step closer to achieving accurate measurements, you’re still unable to use these values as you have no means to determine a fraction of cups and tablespoons. You do have those sets that have measurements of 1, ½, ⅓, ¼, and ⅛ for both measuring cups and tablespoons. Your problem now is how to convert these values into
fractions that will be compatible with what you have.
This is where this tutorial steps in! We’ll teach you how to handle this problem or any other similar situation you may face in the future. But first, we need to teach you about a few symbols we will use to dictate the number formatting we want. Both methods of converting decimals to fractions will utilize these symbols so it’s best to understand them first before using them.
Here are the useful
custom number formatting symbols for converting decimals to fractions in Google Sheets!
Useful Custom Number Formatting Symbols
Google Sheets have a list of available number formatting options like percentage, scientific notation, currency, date, and time formats. Surprisingly, there is no option for fractions. Thankfully, we have the option of inputting our own custom number format.
Multiple symbols may be used like currency, date and time, text, and numeric symbols. For this article, we will only discuss four numeric symbols in detail. The first three are digit placeholders and the last one indicates the use of fractions. These symbols are:
- 0 – a zero symbol is used for mandatory zeros. If the number contains fewer digits than the placeholders in the format, the number is padded with zeros. (e.g., the format code 00000 shows the number 101 as 00101)
- # – a hash is used to indicate optional zeros. This digit placeholder displays only significant zeros and would not display insignificant zeros. (e.g., the format code ###.### shows the number 014.700 as 14.7)
- ? – a question mark is also used to indicate optional zeros. It shares similar features with using the hash. The main difference is that this symbol replaces insignificant zeros with spaces.
- / – a slash is used to show
For all digit placeholders, if there are more digits to the left of the decimal point than the placeholders in the format, the extra digits will be retained.
Custom Number Formatting for Fractions
Below is a list of possible number formats that you can use for different kinds of fractions. Let’s first look at different ways on how to format proper and improper fractions depending on the number of digits that you want to show in both the numerator and the denominator.
- Up to one digit: ?/?, #/#, 0/0
- Up to two digits: ?/??, ??/??, #/##, ##/##, 0/00, 00/00
- Up to three digits: ?/???, ???/???, #/###, ###/###, 0/000, 000/000
Only the number of placeholders in the denominator is required to indicate how many digits shall be considered in the conversion. This should be matched with at least one placeholder in the numerator. Any of the digit placeholders can be used depending on your preference but we recommend using the hash for proper and improper fractions.
Now, let’s determine how to express mixed fractions:
- Up to one digit: # ?/?
- Up to two digits: # ?/??, # ??/??
- Up to three digits: # ?/???, # ???/???
To express the fraction as mixed fractions, simply add a digit placeholder before the fraction. Again, any of the digit placeholders may be used. For mixed fractions, however, we recommend using a hash for the whole digit and question marks for the numerator and denominator. This provides a clear distinction between the two parts.
For both of the number formats above, Google Sheets will round off the decimal to the nearest fraction with the number of digits specified. However, there may be instances where you want a specific value for the denominator, like the scenario earlier. Luckily, you can specify a fixed denominator for the number format.
- Halves: ?/2, #/2
- Quarters: ?/4, #/4
- Eighths: ?/8, #/4
- Sixteenths: ?/16, #/4
- Fifths: ?/5, #/5
- Tenths: ?/10, #/10
- Hundreds: ?/100, #/100
You only need to put the specific number on the denominator to specify the fixed denominator that you wish to obtain. Again, any of the digit placeholders may be used. Feel free to use whatever you wish to use!
Below is a table where we show how different numbers (first row) look like under the number formats specified (first column). Hopefully, this will help you better understand the differences between the different placeholders.
Now, we’re finally ready to convert decimals to fractions in Google Sheets!
How to Convert Decimals to Fractions Using TEXT Function
The first method involves the use of the TEXT function. This function takes the value from another cell and converts it into a text string using a format specified. Now, let’s learn how to use the TEXT function to convert decimals to fractions!
Let’s look at the scenario discussed earlier.
- Simply click on any cell to make it the active cell. For this guide, I will be selecting G2 where I want to show my result.
- Next, simply type the equal sign ‘=’ to begin the function and then followed by the name of the function which is our ‘text’ (or ’TEXT’, whichever works). The one we want is the TEXT function so make sure to click on the TEXT function from the list of available functions that may pop up.
- Select the cell you wish to convert into a fraction. For this example, I will start with cell E2. Afterward, type a comma ‘,’ to indicate that we have finished selecting the number and would like to input the format.
- Now, type the custom number format that you wish to use and enclose it with quotation marks. Since baking requires precise measurements and proportions, I will use a mixed fraction format with a fixed denominator of 8. Type out “# ?/8” in the formula to indicate this format.
- Press Enter to complete the formula. Copy this formula to the remaining data that needs to be converted.
Done! You may notice that some of the values can be further rounded down. Since a fixed denominator of 8 is used, Google Sheets cannot simplify the expression further. But hey, it’s much simpler to round down 6/8 into ¾ than determining the fraction of 0.704845, right?
We recommend using this process only when you won’t do any further calculations with the value you have converted. Because the TEXT function returns a string value, trying to use this value for calculations will cause an error. The second method offers a solution to work around this problem.
Let’s learn how to use
custom number formatting to convert decimals to fractions!
How to Convert Decimals to Fractions Using Custom Number Formatting
Changing the number formatting of a cell in Google Sheets only changes how values are presented without altering it. This means that you can still make calculations with the original value even though it has been presented differently.
Now, let’s apply this method to the scenario discussed earlier.
- Simply select the range of numbers you want to change the number format. For this guide, I will be selecting cells E2:E18 to show my result.
- Under the Format tab, drag your mouse to the Number option. This will cause another list to pop up. Drag the mouse again to the More Formats option at the bottom of the list and select Custom number format.
- Alternatively, the More Formats button above the formula bar may be pressed to navigate through available number formatting options.
- The custom number formats box should pop up. Simply type the custom format you want to show. For this example, I want the values to be presented as a mixed number with a fixed denominator of 8. Type out “# ?/8” in the formula to indicate this format.
- Finally, click on apply for the results to show up.
If you’ve followed the steps properly, we should end up with the same results regardless of any method used. Feel free to explore more custom number formats using the tools we have given in this tutorial!
We’re done! It’s easy to convert decimals to fractions, right? If you want to practice some more, make a copy of our spreadsheet and give it a try:
Or browse our other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.