Show Results as Fractions
September 07, 2023 - by Bill Jelen

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.

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

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.


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.

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.

This article is an excerpt from Power Excel With MrExcel
Title photo by Jerry Zhang on Unsplash