Microsoft Office Tutorials and References
In Depth Information
Decimal point. This symbol determines how many digits (0 or #) appear to the
right and left of the decimal point. If the format contains only # placeholders to
the left of this symbol, Excel begins numbers less than 1 with a decimal point.
To avoid this, use 0 as the first digit placeholder to the left of the decimal point
instead of #. If you want Excel to include commas and display at least one digit
to the left of the decimal point in all cases, specify the format #,##0 .
Percentage indicator. This symbol multiplies the entry by 100 and inserts the
Fraction format character. This symbol displays the fractional part of a
number in a nondecimal format. The number of digit placeholders that
surround this character determines the accuracy of the display. For example,
the decimal fraction 0.269 when formatted with # ?/? is displayed as 1/4, but
when formatted with # ???/??? is displayed as 46/171.
Thousands separator. If the format contains a comma surrounded by #, 0,
or ? placeholders, Excel uses commas to separate hundreds from thousands,
thousands from millions, and so on. In addition, the comma acts as a rounding
and scaling agent. Use one comma at the end of a format to tell Excel to round
a number and display it in thousands; use two commas to tell Excel to round
to the nearest million. For example, the format code #,###,### , would round
4567890 to 4,568, whereas the format code #,###,###,, would round it to 5.
Scientific format characters. If a format contains one 0 or # to the right of an
E–, E+, e–, or e+, Excel displays the number in scientific notation and inserts E
or e in the displayed value. The number of 0 or # placeholders to the right of
the E or e determines the minimum number of digits in the exponent. Use E–
or e– to place a negative sign by negative exponents; use E+ or e+ to place a
negative sign by negative exponents and a positive sign by positive exponents.
$ – + / ( )
Standard formatting characters. Typing any of these symbols adds the actual
corresponding character directly to your format.
Literal demarcation character. Precede each character you want to display
in the cell—except for : $ – + / ( ) and space—with a backslash. (Excel does
not display the backslash.) For example, the format code #,##0 \D;–#,##0 \C
displays positive numbers followed by a space and a D and displays negative
numbers followed by a space and a C . To insert several characters, use the
quotation-mark technique described in the "Text" table entry.
Underscore. This code leaves space equal to the width of the next character.
For example, _) leaves a space equal to the width of the close parenthesis. Use
this formatting character for alignment purposes.
Literal character string. This formatting code works like the backslash
technique except that all text can be included within one set of double
quotation marks without using a separate demarcation character for each
Repetition initiator. This code repeats the next character in the format
enough times to ill the column width. Use only one asterisk in the format.
Text placeholder. If the cell contains text, this placeholder inserts that text in
the format where the @ appears. For example, the format code “This is a” @
displays “This is a debit” in a cell containing the word debit .