How to Use Fractions in Excel? An Easy Guide
(Note: This guide on how to use fractions in Excel is suitable for all Excel versions including Office 365)
Fractions in Excel display the numbers without converting them into decimals. Excel fractions consist of one-digit, two-digit, three-digit, halves, quarters, etc.
When you work with numbers in Excel, you probably have stored whole numbers, decimal numbers, and percentages. Have you ever worked with fractions and stored them as they are with the numerator and the denominator without converting them into decimals? In this tutorial, let’s look into how to store fractions as ½ (one-half) or ⅔ (two-thirds).
You’ll Learn:
Related Reads:
How to Use CONVERT Function in Excel? A Step-by-Step Guide
How to Save Excel Chart as Image? 4 Simple Ways
How to Insert a Hyperlink in Excel? 3 Easy Ways
What Is a Fraction in Excel?
Let’s look at what fractions are and why fractions in Excel are important. A fraction represents the parts of a whole number or collection of objects. It represents the total number of equal parts the whole number is divided into. The number on the top of the line is called the numerator, and the number below the line is called the denominator. The different types of fractions are:
Proper fraction – if the number at the top is less than the number at the bottom, it is called a proper fraction. For example, ⅜ is a proper fraction, where 8 represents the whole parts, from which 3 parts are taken or used.
Mixed fractions – here, there is a number before the fraction. For example, 1 ¾ is a mixed fraction, and 1 represents a whole number, and ¾ is the fraction.
Improper fractions – if the number at the top is larger than the number at the bottom, it is called an improper fraction. For example, 6/4 is an improper fraction. It represents 6 parts divided from 4 whole parts.
How to Format the Cell to Accept a Fraction?
When you enter two numbers separated by a slash, it converts that number into decimal format. Select the cell whose value we need to convert to fractions to retain fractions. Right-click on the cell and select the Format Cells option from the menu.
Excel spreadsheets accept whole numbers and decimal numbers. A fraction has a numerator and a denominator, so when you enter two numbers, Excel understands it as a date and displays the date or just a garbage value.
Format the cell where you are going to enter a fraction. Or you can select the cells that contain the values, then right-click on it, and you get the dialog box Format Cells. In the Number tab, select the option Fraction. The decimal numbers you entered should appear as fractions.
Another method of converting decimal numbers into fractions is to select the Home tab and select the option Fraction in the Numbers group. Before that, select the cells that contain decimal numbers.
You can also make mixed fraction numbers, like the one shown in the example, 21/4. You can even use fractions in Excel formulas and functions.
You can also use a formula using fractions. You can multiply the value in cell A3 by 2 as =A3*2. Then, you can use the fill handle to add the formula to other cells.
Suggested Reads:
How to Add Page Numbers in Excel? 2 Easy with Options
How to Use the Excel TREND Function? A Step-by-Step Guide
How to Hide Formula Bar in Excel (and Show)? 3 Easy Ways
Few Things to Remember
1. When you enter a fraction number in a cell, make sure to check the formatting of the cell. If it has been formatted as Date by default, it would consider the value 2/4 as 02-04-2022. The fraction number you enter will be converted to a currency value if the cell has been formatted as currency.
The same formatting happens when the cell is formatted to contain Text. when you enter a value of 2/4, it appears as 2/4 itself. But, when you enter the same number 2/4 and apply the formatting to the cell to contain a fraction, it converts it to ½, and in the formula bar, the value appears as 0.5.
2. Fractions in Excel round the value up or down if it cannot display the right fraction. For example, if you type 6/11 in cell A2, it displays the result as ½, and the formula bar shows the correct decimal value.
3. When you enter a mixed fraction into a cell, Excel understands that we want to display a fraction and automatically changes the number format. For example, entering 1 ¾ automatically converts the format of the cell to a fraction. Excel thinks that you have entered a date or text if you enter a fraction without a leading digit. Therefore, enter a mixed fraction so that Excel understands that you want to display a fraction, automatically changing the cell’s formatting.
Also Read:
How to Find and Replace in Excel? A Step-by-Step Guide
How to Randomize a List in Excel? 2 Different Methods
How to Use Excel Split Screen? 3 Simple Ways
Frequently Asked Questions
How do you convert decimals to fractions?
When you enter a decimal value in a cell in an Excel spreadsheet, it appears as a decimal. First, right-click the cell to convert it into a fraction, then click on Format Cells. Choose the Fraction option, then enter the value. The fraction appears in the cell, though the decimal value appears in the formula bar.
How do you multiply fractions in Excel?
Multiplying fractions in Excel is the same as multiplying whole numbers. Mention the cell reference in the formula as shown below. In the example below, two fractions ⅕ and ⅗, are to be multiplied, and the formula to perform this is entered in the formula bar = E2*F2.
When you perform this multiplication manually, the steps would be = ⅕ * ⅗. The answer is 3/25, which in turn is reduced to 1/9. When the denominators of the two values are the same, you can directly multiply the numerators and the denominators.
How do I convert fractions to percentages in Excel?
It is better to display a value as a percentage rather than representing it as decimals. Rather than displaying a value in fraction as ¾, which is 0.75 in decimal notation, it is better to represent it as 75% in decimal format.
To convert the fraction into a percentage, select Home -> Numbers (group ) -> Percentage.
Closing Thoughts
Fractions in Excel are used when numbers cannot be represented as whole numbers. Numbers can be represented as whole, decimal, and as fractions. When you work with mathematical formulas, you can expect fractional values that you have to simplify. Learning how to enter a fraction and the different formats in which numbers can be stored would be useful.
Please visit our free resources center for more high-quality Excel guides.
Ready to take the next step and hone your skills in Excel?
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 140+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
