Excel 2010 Bible

Chapter 24: Using Custom Number Formats

565

You can use a custom format string to create other fractional formats. For example, the following

format string displays a value in 50ths:

# ??/50

To display the fraction reduced to its lowest terms, use a question mark after the slash symbol. For

example, the value 0.125 can be expressed as 2/16, and 2/16 can be reduced to 1/8. Here’s an

example of a number format that displays the value as a fraction reduced to its simplest terms:

# ?/?

If you omit the leading hash symbol, the value is displayed without a leading value. For example,

the value 2.5 would display as 5/2 using this number format code:

?/?

The following format string displays a value in terms of fractional dollars. For example, the value

154.87 is displayed as 154 and 87/100 Dollars.

0 “and “??/100 “Dollars”

The following example displays the value in sixteenths, with a quotation mark appended to the

right. This format string is useful when you deal with fractions of inches (for example, 2/16").

# ??/16\”

Displaying a negative sign on the right

The following format string displays negative values with the negative sign to the right of the num-

ber. Positive values have an additional space on the right, so both positive and negative numbers

align properly on the right.

0.00_-;0.00-

When you create a custom number format, don’t overlook the Sample box on the Number tab in the

Format Cells dialog box. This box displays the value in the active cell using the format string in the

Type box.

It’s a good idea to test your custom number formats by using the following data: a positive value, a

negative value, a zero value, and text. Often, creating a custom number format takes several attempts.

Each time you edit a format string, it is added to the list. When you finally get the correct format string,

access the Format Cells dialog box one more time and delete your previous attempts.

Testing Custom Number Formats