MrExcel.com - Your One Stop for Excel Tips & Solutions

Show Results as Fractions

September 07, 2023 - by

Show Results as Fractions

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.

Format Cells, Number tab, Fraction category. Choices are Up to One Digit (1/4), Up to 2 digits (21/25), Up to three digits (312/943) then halves, quarters, eighths, sixteenths.
Figure 1312. Built-in fraction formats.

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

Several examples of fractions. A decimal such as 0.548679 could be 5/9 or 45/82 by changing which fractional format.
Figure 1313. 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.

To display in 1/32nds, use a custom type of # ??/32
Figure 1314. Adapt this format for any fraction.
Tire tread depths displayed in /32nds
Figure 1315. Display numbers as 32ths.

Problem: I have to enter values for milliamps. For the 35 mA in row 2, you type 35E-3. For the 150 mA in row 3, you type 150E-3. In both cases, Excel changes what you typed to the values shown in column C.

A shortcut for entering scientific notation. You type 35E-3 and Excel displays 3.50E-02
Figure 1316. If you look quickly, the 1.5 appears smaller than 3.5.

Format the cells using Ctrl+1. Choose the Number tab and then the Custom category. Change the custom format code from 0.00E+00 to ##0.0E+00.Excel will now display the cells as you wish.

Change the custom format code to ##0.0E+00 and you will get 30.0E-03
Figure 1317. Column E

This article is an excerpt from Power Excel With MrExcel

Title photo by Jerry Zhang on Unsplash