Tip Printed from ExcelTip.com
Symbols Used to Create Custom Formatting
The following symbols are used in Custom Formatting:
0 (zero) symbol: Displays a digit in a cell, including the digit 0. For example, the format 0.00 displays the number 0.987 as a number with two places after the decimal point, that is, 0.99 (without the 7). Places are omitted after the number is rounded. Any omitted insignificant digits cause the number to be rounded. In this case, 0.98 was rounded to 0.99.
# (pound) sign: Displays significant digits and does not display insignificant zeros. For example, a format with two places after the decimal point, with or without the digit 0, for example, the format code for 50 cents:
#.##: The cell displays .5.
#.#0: The cell displays .50.
0.00: The cell displays 0.50.
, (comma): Separates thousands. For example, with the format #,##0, the number 4543 is displayed as 4,543. The comma has a second use in the number format. If you place the comma at the end of the digits the displayed number is by thousands.
#,##0,: Displays numbers in thousands.
#,##0,,: Displays numbers in millions.
/ (forward slash) symbol: The division sign for displaying a fraction.
(asterisk) symbol: Fills in empty characters, up to the beginning of the number. For example, the number 4,543 is displayed as $4,543 with the format $ *#,##0. The $ sign is displayed on the left side of the cell, and the number is displayed on the right side.
"TEXT": If text characters are enclosed in quotation marks and followed by a number format, the text is displayed and the digits are formatted. For example, with the format "Balance" #,##0, the number 4,543 is displayed as Balance 4,543. In the sheet cell, you only need to enter the digits, not the text.
(backslash) Any single character: Use the backslash followed by a single text character to display that character. For example, the following format will display a number in millions: #,##0.0,,M. Using this format, 123,789,456 would be displayed as "123.8M".
Excel allows you to omit the backslash when displaying a capital letter K. This format will display a number in thousands: #,##0,K. Using this format, 123,789,456 would be displayed as 123,789K.