In case you try and enter any fraction in a cell in Excel, there is a possibility that Excel may change it to a date or a text string. This happens because fractions are not one of the data types that Excel understands. So when you give it a fraction, it tries and converts it into a date or a text string which are the data types that are acceptable for Excel. So what if you want to get your numbers to get displayed as fractions in excel? Don’t worry, there is a way around it! In this short tutorial, I’m going to show you a couple of methods you can use to display numbers as fractions in Microsoft Excel.
Show Decimals as Fractions by Changing the Cell Format
Fractions are essentially decimal numbers. for example, 1/2 is 0.5, and 1/4 is 0.25 While fractions may be something that Excel does not understand by default, decimals are an acceptable data type. So, you can have a decimal number in a cell in Excel and format it in such a way that it shows up as the desired fraction. Suppose, I have a data set of some decimals in column A (as shown below) that I want to show as fractions. Below are the steps to do this: The above steps would instantly convert all the decimal numbers into fractions. Now there are two things you need to know when you’re using the inbuilt Fraction format to convert decimals into fractions: Note: When you change the format of a cell and make a decimal number show up as a fraction, it only changed the way it is being shown to the user. It does not change the original value in the cell. For example, if a cell has 0.9 as the decimal value and you apply fraction format that shows it as 8/9, the value in the cell still remains 0.9
Other In-Built Fraction Formats in Excel
The default fraction format that you can choose from the drop-down in the ribbon would always give you a fraction where the denominator is a single digit. But there are other inbuilt fraction formats that you can also use. Suppose I have a below data set for decimals in column A: Below are the steps to access and use other inbuilt fraction formats in Excel: Based on whatever fraction format you choose, the numbers in column A would be displayed in that format. Below are all the fraction formats that are available to you:
Up to one digit (1/4)Up to two digits (21/25)Up to three digits (312/943)As halves (1/2)As quarters (1/4)As eights (1/8)As sixteenths (1/16)As tenths (1/10)As hundreds (1/100)
Creating Your Own Fraction Format in Excel
In most cases, the inbuilt fraction formats are sufficient. But sometimes, there may be a need where you have to have a specific number as the denominator in the fraction format (say I want the denominator to always be 30). Since I do not have any inbuilt format where the denominator can be 30, I can create my own fraction format in this case. Below are the steps to create your own custom fraction format in Excel: The above format would convert all the decimal numbers into a fraction where the denominator is always 30. Let me quickly explain how this custom number format works:
The # at the beginning is needed to show mixed fractions (such as 2 1/30 or 17 12/30). This tells the format that In case the decimal number has an integer portion, show it alongside the fraction. A # represents any number of digits in the format??/30 would force the formatting to always have 30 as the denominator and have space for two digits as the numerator. You can also keep just one ?, but having two question marks would mean that in case you have fractions with one of two digits in the numerator, they would always align in a column (as you can see in the image above).
Similarly, you can create your own custom format from fractions and specify the denominator you want.
Show Decimals as Fractions Using the TEXT Formula
Another way to convert decimal numbers into fractions is by using the TEXT function in Excel. Just like the custom number format method I covered above, the TEXT function allows you to specify the format in which you want to show the decimal number. One major difference between the TEXT function method and the Custom Number Format method is that the result of the TEXT formula is a text string. So if you convert 0.1 into 1/10 using the TEXT formula, it would be a text string and you won’t be able to use it in calculations. Let me first show you how this method works and then I will show you why you may want to use it in some cases. Suppose you have the decimals in column A as shown below, that you want to convert to fractions: Below is the formula that will do this: The above formula takes the value in cell A2, and then shows it in the specified format. In the above example, I have used the format that shows the fraction that can only contain one digit in numerator and denominator. You can specify any fraction format you need as the second argument of the formula (and make sure it’s in double quotes). Now coming back to the question – “What’s the benefit of using the TEXT formula to show numbers/decimals as fractions?“. Since the output of the text formula is a text string, you can add a prefix or suffix to the result of this formula. Let me show you why that’s important. Suppose you have the data set as shown below, where you have the product ID in column A and the value in column B. Below is the formula that you can use to combine the product ID and the value (as a fraction) and get the result in the same cell. Since the text formula converted the fraction output into a text string, we were able to append another text string before it. This is something you will not be able to do if you use the custom format method to convert decimal to fractions.
Writing Fractions in Excel (without Excel changing it)
If you try and enter some fractions in a cell in excel, you would notice that they are automatically converted into a date. For example, if you enter 1/10, it will automatically be converted to 10-Jan or 01-Oct (depending on your regional calendar format) This happens as Excel considers 1/10 as a valid date format, and converts it into one. But what if you do not want Excel to convert it into a date. What if you still want to continue using it as a fraction? One way could be to enter the decimal number and then convert it into a fraction using the methods covered above. Let me show you two more methods that you can quickly enter a fraction in a cell in excel without excel changing it into a date:
The simplest way would be to start the data entry by first entering an apostrophe followed by the fraction. When you add an apostrophe in a cell, anything that follows it is converted into a text string. Since date and time values are stored as numbers in excel, a text string would not be converted into a date
The other method would be to first change the format of the cell and make it text. To do this, click the Home tab and in the Number group, click on the Format drop-down and select Text. Once done, anything you enter in that cell would be considered a text string and the fraction would not be converted into a date
In this tutorial, I showed you how to show decimal numbers as fractions using two methods (custom formatting and TEXT formula). I also covered how to enter fractions in Excel without it getting changed into a date format. I hope you found this tutorial useful. Other Excel tutorials you may also like:
How to Stop Excel from Rounding Numbers (Decimals/Fractions)How to Add Decimal Places in Excel (Automatically)How to Stop Excel from Changing Numbers to Dates AutomaticallyHow to Make Negative Numbers Show Up in Red in Excel7 Amazing Excel Custom Number Format Tricks (you Must know)Convert Time to Decimal Number in Excel (Hours, Minutes, Seconds)How to Round to the Nearest Integer or Multiple of 0.5 / 5 / 10 in ExcelConvert Scientific Notation to Number or Text in ExcelShow Negative Numbers in Parentheses (Brackets) in Excel
