Excel: Show Results as Fractions

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: I work in an industry that reports values in fractions. Stockbrokers used to deal in increments of 1/8, and tire engineers still measure tread depth in increments of 1/32 inch.

Strategy: There are number formats for fractions. When you press Ctrl+1 to display the Format Cells dialog, you will see that there are nine standard fraction formats available in the Number tab of the Format Cells dialog box.

  1. Built-in fraction formats.

    When you choose a fraction format, Excel finds the closest fraction.

  2. 0.548679 is about 5/9 or 45/82.

    Beyond the seven shown above, Excel offers standard formats for 10ths and 100ths. Unfortunately, there is not a standard format for 32ths.

    You can create a custom numeric format to handle 32ths:

    1. Select the standard format for 16ths.
    2. In the Category list on the Number tab of the Format Cells dialog, scroll down and select Custom. The custom number format code for 16ths is # ??/16. From this, you can deduce that # ??/32 might be a valid number format.
    3. Click in the Type box and change the 16 to 32. The Sample area will immediately confirm that you have hit upon the correct format for 32ths.

  3. Adapt this format for any fraction.

  4. Display numbers as 32ths.