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:

• Proper Fractions: If the numerator (top number) in any fraction is less than the denominator (bottom number), it is referred to as the Proper Fraction. For example, 2/4 represents the proper fraction, where ‘2’ is less than ‘4’. This particular fraction refers to a number that is equal to two parts out of the whole, while the whole is separated into four equal portions.
• Improper Fractions: If the numerator (top number) in any fraction is greater than or equal to the denominator (bottom number), it is referred to as the Improper Fraction. For example, 4/2 represents the improper fraction, where ‘4’ is bigger than ‘2’. This particular fraction refers to a number that is equal to four parts out of the whole, while the whole is separated into two equal portions.
• Mixed Fractions: If any fraction consists of an additional number attached before a fraction, it is referred to as the Mixed Fraction. For example, 1˝ represents the mixed fraction, where the number ‘1’ is presented before the fraction ‘1/2’. This particular fraction represents one whole, plus one part of a whole, where a whole is divided into two parts. It can also be written as an improper function, i.e., 3/2.

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:

• Format Numbers as Fractions using the Ribbon
• Format Numbers as Fractions using the Format Cells

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:

• First, we need to select/ highlight all the cells where we want to apply fractions. In our example, we select the cells from A2 to A10.
  
• After selecting all the effective cells, we must go to the Home tab and click the drop-down icon next to Number Format under the category Number. It looks like this:
  
• In the drop-down list, Excel displays some common pre-defined number formats. We can choose the desired number format by clicking on the respective option. In our case, we choose the Fraction option from the bottom of the drop-down list.
  
• The selected decimal point values are immediately converted to fractions when we click on Fraction.
  
  This only changes how decimal points are represented as fractions while the value remains the same. If we click on a specific fraction in cells A2 to A10, we can see the decimal point value from the formula bar.
  

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:

• Like the previous method, we are first required to select the effective cells in the worksheet. Therefore, we select the cells from A1 to C8.
  
• Next, we must right-click on selected cells and click the Format Cells option from the list.
  
  Alternately, we can press the keyboard shortcut ‘Ctrl + 1’ instead of right-clicking menu options. This step helps us to launch the Format Cells dialogue box.
• Under the Format Cells window, we need to click on the Number tab and choose the Fraction option from the list.
  
• After clicking on the Fraction option, Excel displays another list with additional options for the fraction. Using the list, we can apply the fraction on selected cells up to one digit, two digits, three digits, etc. In our example, we choose the first option, i.e., Up to one digit (1/4).
  
• Lastly, we must click the OK button to apply the selected number format on the selected cells within the worksheet. After that, we will see fraction numbers in our example sheet instead of the decimal numbers.
  

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

• It is recommended to set fractions up to three digits for better adjustment and effective calculations using Excel.
• The corresponding decimal number/ value can be seen in the Formula bar when entering any fraction in an Excel cell.