Fractions in Excel
MS Excel or Microsoft Excel is powerful spreadsheet software that helps users record data in different cells across multiple sheets. While working in Excel, we generally come across different values, such as Integer, Decimal, Percentage, etc. Excel can handle almost all data types or values. The fractions are also one of the common number formats in Excel, and there may be certain times when we may need to work with fractions in Excel.
In this article, we discuss the brief introduction of Fractions in Excel and how to use them in an Excel worksheet properly. Using these methods, we can enter numbers in Excel cells and display them as fractions.
Introduction to Fractions in Excel
Fractions in Excel refer to a specific number format that helps us display the entered numbers as exact fractions instead of dates, decimal numbers, or any other format. There are various formatting options for fractions in Excel, including the fractions up to one digit, two digits, three digits, etc.
In simple words, fractions help display values (or numbers) as the number of sections of a whole. The numbers are separated using the slash (/) sign while representing the fractions. For example, P/Q represents a fraction where Q refers to a whole portion, while P refers to a part of the whole portion Q. In a fraction, the top number (i.e., P) is called the numerator, and the bottom number (i.e., Q) is called the denominator.
Likewise, while working in Excel, we may need to write 1/2 instead of 0.5, 3/4 instead of 0.75, etc. All these are examples of fractions.
There are typically three types of fractions, namely:
How to Format Numbers as Fractions in Excel?
Since fractions are a special case in MS Excel, they are not displayed in Excel by default. When we try to enter fractions in Excel cells, Excel automatically converts them to another format like text, date, or decimal. However, sometimes we may need to display fractions to help users read the information easily. Although fractions displayed as text look good on a worksheet, they cannot be used in calculations.
So, to add fractions in Excel correctly, we need to format the respective Excel cells as fractions only so that Excel does not change the fractions by itself. In this way, fractions are displayed appropriately and can also participate in desired calculations.
The following are two effective methods for formatting cells (or numbers) as fractions in an Excel worksheet:
Let us discuss each method in detail:
Format Numbers as Fractions using the Ribbon
The ribbon is the top area that contains various tabs and related commands or features. The Ribbon has almost all built-in tools or commands to perform most tasks in Excel. We can also choose between different number formats, including fractions, directly from the ribbon.
For example, suppose we have the following Excel sheet with some decimal points numbers in cells A2 to A10.
Using the Excel ribbon, we need to format these numbers or convert these numbers into fractions using the Excel ribbon. For this, we must follow the below steps to format given numbers as fractions:
Although we can format numbers as fractions directly from the Home tab on the ribbon, it gives limited access to relevant preferences. Instead, we can use the Format Cells dialogue box and format numbers as fractions using additional or advanced features.
Format Numbers as Fractions using the Format Cells
Another method to format numbers as fractions is to use the Format Cells dialogue box. The Format Cells dialogue box consists of various formatting-based numbers format settings. We can format the desired Excel cells or numbers as fractions by using these settings.
Let us again take another example of a worksheet where we have decimal digits in cells from A1 to C8, as shown below:
We need to format all the decimal digits as fractions using the Format Cells dialogue box. Therefore, we must follow the below steps:
In this way, we can format numbers as fractions by using the Format Cells dialogue box. If we don't find the pre-defined options helpful and need to use any specific fraction format, we can define our custom formatting of the fraction.
How to use Custom Fractions in Excel?
Using the Custom Number Format in Excel, we can define any particular number as a denominator. Also, we can set any specific function where the numerator is greater than the denominator, i.e., 3/2.
To use the custom fraction number format, we need to select the effective cells and launch the Format Cells dialogue box like the previous method. However, now, we need to choose the Custom option from the list under the Number tab. After that, we need to specify the custom format code inside the Type box.
When creating custom format codes, we can use the dash character (#) to represent a whole number and a question mark (?) to specify the maximum number of digits for the numerator and denominator.
Some examples of custom fraction formats are displayed below:
Important Points to Remember