Exceljet

Quick, clean, and to the point
From the video: How to use fraction formatting in Excel

How to use fraction formatting in Excel

In this lesson we'll look at the Fraction format. The Fraction format is made to display fractions. It can handle fractions of up to three digits, and comes with several presets for common fraction units.

Let's take a look.

In column B of our table we have a set of numbers in general format that can be expressed as fractions. Let's first copy these numbers to the rest of our table.


Now let's apply the fraction format in columns C through G using the number format menu on the ribbon.

As we can see, Excel converts most of the values to fractions. However, some numbers aren't converted, while others are just a close match.

If we check the options available for the fraction format in the Format Cells dialog box, we see that there are a number of built-in combinations, and the current selection is for fractions "up to 1 digit". That's the correct setting for column C.


For column D, we want fractions up to two digits.


In the same way we can set fractions up to three digits for Column E.


Looking at the table, we can see some fractions are more accurate than others. For example, .95 was first expressed as 1, then as 32/33, then as 97/100ths.

Excel will display the best match available for each fraction format.

Let's try quarters and eighths for the last two columns. We'll use the keyboard shortcut ctrl-1 to get to Format Cells.


Again, we can see that many fractions are estimates, while others are exact.


You can enter fractions directly in cells pre-formatted with the fraction format. You may want to adjust the fraction type in some cases.


For cells using General format, you might have trouble typing in a fraction, because Excel may interpret the fraction as a date. You can work around this problem by entering a zero and space first before the fraction.

for example, to enter four fifths, enter "zero space 4/5"

Related shortcuts

Author 
Dave Bruns