How to Show Fractions in Numbers (Write Fractions in Excel)

It could be difficult to get numbers to display as fractions in Excel.

Excel will convert every fraction you try to insert into a cell into a date or a text string.

This occurs due to Excel’s inability to handle fractional data types. Therefore, when you give it a fraction, it tries to transform it into a date or a text string because those are the only data types that Excel will accept.

What does it matter if you want Excel to display your numbers as fractions?

No need to be concerned—a solution exists!

In this brief tutorial, I will walk you through a few techniques for displaying integers as fractions in Microsoft Excel.

This instruction explains:

1. By altering the cell format, you may display decimals as fractions.
       1.1 Other Excel Built-In Fraction Formats
       1.2 Using Excel to Create Your Fraction Format
2 Using the TEXT Formula, Display Decimals as Fractions
3. Fractions in Excel Written (without Excel changing it)

1. By altering the cell format, you may display decimals as fractions.

In essence, fractions are decimal numbers. 1/2, for instance, is 0.5, and 1/4, is 0.25
Although Excel may not be able to handle fractions by default, decimals are a valid data type.

Therefore, you may arrange a decimal value in an Excel cell such that it appears as the required fraction.

Let’s say I want to display some of the decimals in column A of the data set below as fractions.

$Let's say I want to display some of the decimals in column A of the data set below as fractions.$

The procedures are as follows:

Choose the cells that contain decimals.
On the Home tab, click
Click on the format drop-down in the Number group.

Choose fractions

All of the decimal numbers would be quickly converted to fractions using the techniques above.

When converting decimals to fractions using the built-in Fraction format, you should be aware of two things:

Any fraction with a single-digit denominator will always be obtained.
If the decimal cannot be transformed precisely into a fractional value (while maintaining a single digit in the denominator), it will be rounded to the nearest fraction. For instance, using the aforementioned procedure, you would get 8/9 if you had the decimal 0.9 (which is the decimal equivalent of 9/10). (which is an approximate fraction while keeping the denominator to a single digit).

It should be noted that formatting a cell to display a decimal value as a fraction
only affects how the user sees the number. The cell's initial value is unaffected by it.
For instance, if a cell's decimal value is 0.9
and you use a fraction format to display it as 8/9, the value in the cell stays at 0.9.

Other Excel Built-In Fraction Formats:

When using the drop-down menu on the ribbon to select the default fraction format, you will always receive a fraction with a single digit as the denominator.

But you can also use some additional built-in fraction forms.

Let’s say I have the decimal data set below in column A:

The methods to use additional built-in fraction formats in Excel are listed below:

Choose every cell in column A.

On the Home tab, click

Click on the dialogue box launcher (which resembles a titled arrow) under the Number group. This will bring up the dialogue box for “Format Cells.”

Click on the “Fraction” category under the “Number” tab in the Format Cells dialogue box.

Choose any of the fraction forms that are currently provided.

The numbers in column A would be displayed according to the fraction format you select.

You can use any of the fraction formats listed below:

One digit (1/4) maximum
Two digits or less (21/25)
Three digits or less (312/943)
Half (1/2) as
14 as quarters
1/8 as eights
One sixteenth (1/16)
10ths (1/10)
100ths of hundreds

Using Excel to Create Your Fraction Format

The built-in fraction forms are usually enough.

However, there may be circumstances in which you must use a certain integer as the fraction’s denominator (say I want the denominator to always be 30).

I can construct my fraction format in this situation as I don’t have any built-in formats that allow for a denominator of 30.

The steps to make your unique fraction format in Excel are listed below:

MS Excel VBA Tutorial

Select all of the data-filled cells (in this example, I will select all the item numbers I have in column A)
On the Home tab, click
Click the dialogue box launcher under the Number group (that looks like a titled arrow).
Click the Custom category under the Number tab in the Format Cells dialogue box that appears.
Enter the following code in the Type field: #??/30.
Press “OK”

All of the decimal values would be converted using the method above into a fraction with a constant denominator of 30.

Let me simply describe the operation of this unique number format:

To display mixed fractions (such as 2 1/30 or 17 12/30), use the # at the beginning. This instructs the format to display the integer component of the decimal number together with the fraction if it exists. In the format, a # denotes any number of digits.

The formatting would be forced to always use 30 as the denominator and leave room for two digits in the numerator if the format was??/30. You can choose to have one question mark instead of two, but keeping two ensures that fractions with one or two digits in the numerator will always line in a column (as you can see in the image above).

Similar to this, you may use fractions to build your bespoke format and choose the denominator you choose.

MS Excel VBA Tutorial

Using the TEXT Formula, Display Decimals as Fractions:

Using Excel’s TEXT function is another method for converting decimal integers to fractions.

You may select the format in which you wish to display the decimal number using the TEXT function, just like with the customs number format technique I discussed earlier.

The fact that the output of the TEXT formula is a text string distinguishes it significantly from the Custom Number Format approach.

As a result, employing the TEXT formula to convert 0.1 to 1/10 would result in a text string that cannot be used in computations.

Let me first demonstrate how this approach functions before explaining why you might find it useful in some circumstances.

Assume you wish to convert the decimals in column A below from decimals to fractions:

The equation to accomplish this is given below:

MS Excel VBA Tutorial

=TEXT(A2,"# ?/?")

The value from cell A2 is taken by the formula above, which then displays it in the desired manner. I’ve used a format in the example above that displays fractions with a numerator and denominator that can only have one digit each.

The second argument of the formula can be any fraction format you require (just make sure it’s enclosed in double quotes).

Returning to the original query, what is the advantage of displaying integers and decimals as fractions using the TEXT formula?

You can prefix or suffix the formula’s result since the text formula’s output is a text string.

Let me explain why it is significant.

Consider the following data collection, where column A contains the product ID and column B contains the value.

The formula to combine the product ID and the value (as a fraction) and obtain the result in the same cell is shown below.

=A2&" – "&TEXT(B2,"# ?/?")

MS Excel VBA Tutorial

We were able to insert a text string before the text formula converted the output fraction into a text string.

If you convert decimals to fractions using the custom format approach, you won’t be able to accomplish this.

Fractions in Excel Written (without Excel changing it)

Excel will automatically transform fractions you enter into dates if you try to enter them in a cell.

For instance, if you type 1/10, it will be transformed to 10-Jan or 01-Oct automatically (depending on your regional calendar format)

This occurs as a result of Excel converting 1/10 into a valid date format.

What happens, though, if you don’t want Excel to turn it into a date?

What happens if you want to keep using it as a fraction?

Entering the decimal number and then converting it to a fraction using the techniques described above could be one approach.

Let me demonstrate two more quick ways to enter a fraction in an Excel cell without having Excel convert it to date:

The simplest method to begin inputting data would be to put an apostrophe before the fraction. Anything that follows an apostrophe in a cell is transformed into a text string. Excel stores date and time information as numbers, therefore converting a text string to a date would not be possible.

The alternative approach would be to convert the cell’s format to text first. To do this, pick Text from the Format drop-down menu under the Number group on the Home tab. When you’re finished, everything you type in that cell will be treated as text and the fraction won’t be turned into a date.