Format Numbers to Display as Fractions in Excel
July 28th, 2010 Filed Under Computer Tips
Among the twelve basic formats available in Excel for displaying numbers and text, the fraction is one of the least used. Some floating point representations of numbers can become unwieldy unless you limit the precision displayed in a cell. Learn how to use fractions in Excel to make numbers easier to read and interpret.
Basic Fractional Format in Excel
Suppose you have a list of numbers like the one shown in the figure below. Notice that these numbers represent varying degrees of precision from an integer only to a number that is precise to the 10th decimal place. This mix can make it difficult for you to understand the magnitude of the numbers because the decimal place, a tiny dot, is the most important indicator.
Most people simply limit the number of decimal places displayed in a cell even though Excel retains the original precision of the numbers in a worksheet. However, there is another way to limit how Excel displays numbers that may be more appropriate and convenient in some situations.
To use the example above, select the cells A1 through A11 and click on the Home tab on the Ribbon. Locate the section of the Ribbon labeled Number and use the drop down menu to select the option titled Fraction.
You’ll notice that Excel immediately displays the numbers in the selection using fractions for the fractional part of the numbers. Notice, however, that the fractions are limited to one digit in the denominator (bottom number of the fraction). Notice also that the non-decimal part of the numbers (the integers) remain intact.
Formatting the numbers in an Excel worksheet to one-digit-only fractions isn’t very useful unless you specifically need that level of fractional precision. Luckily, Excel offers you the ability to choose the fractional precision of numbers.
Advanced Fractional Formats in Excel
Once again select cells A1 through A11 and click on the number format drop down menu on the Number section of the Ribbon’s Home tab. This time, however, select the More Number Formats option at the bottom of the menu.
You should now be looking at the Format Cells window. If you aren’t already looking at it, click on the Number tab in this window. Here you will see the twelve basic number formats available in Excel.
Since you selected the cells before you opened this window, you will notice that Fraction is already selected in the Category section of the window and that the specific Type of fraction is UP To One Digit (1/4).
Notice that there are nine types of fractions you can have Excel display for you. These nine types of fractions and the smallest fraction possible for each are:
- Up to One Digit – 1/9
- Up to Two Digits – 1/99
- Up to Three Digits – 1/999
- As Halves – 1/2
- As Quarters – 1/4
- As Eighths – 1/8
- As Sixteenths – 1/16
- As Tenths – 1/10
- As Hundredths -1/100
Although it is beyond the scope of this article to describe every use for these fractions and their precision levels, there is one example that makes using fractions in Excel particularly useful.
Rather than use numbers after a decimal point, stock quotes in the newspaper often offer precision to the nearest sixteenth of a dollar ($0.0625), or six and one half cents. If you work in the financial industry or are a business/management major in college, you may find that forcing Excel to display decimal numbers to the nearest sixteenth will mirror those stock prices. T
his way, you won’t need to translate them to the standard form while making calculations. Remember that underneath, Excel still retains the full precision of the numbers you place in a worksheet. Formatting the cells only changes how numbers are displayed, not how much precision the numbers have.
By formatting numbers using fractions, you can make cells in an Excel worksheet easier to read. You may find that you get more done when you don’t have to squint to see a decimal point. Some people working with financial data often find that mirroring the format of stock prices as found in a newspaper makes financial calculations easier to understand and interpret.