Microsoft Office Tutorials and References
In Depth Information
To Create and Assign a Custom Format Code and a Comma Style Format
Excel assigns a format code to every format style listed in the Category list in the Number sheet in the Format
Cells dialog box. As shown in Table 6–2, a format code is a series of format symbols that deﬁ nes how a cell entry
assigned a format will appear. To view the entire list of format codes that come with Excel, select Custom in the
Category list (Figure 6–20 on the previous page).
Table 6–2 Format Symbols in Format Codes
Example of Symbol
# (number sign)
Serves as a digit placeholder. If the value in a cell has more digits to the right of the
decimal point than number signs in the format, Excel rounds the number. Extra digits to
the left of the decimal point are displayed.
Functions like a number sign (#), except that if the number is less than 1, Excel displays a 0
in the ones place.
Ensures Excel will display a decimal point in the number. The placement of period symbols
determines how many digits appear to the left and right of the decimal point.
Displays numbers as percentages of 100. Excel multiplies the value of the cell by 100 and
displays a percent sign after the number.
Displays a comma as a thousands separator.
Displays parentheses around negative numbers.
$ or + or –
Displays a ﬂ oating sign ($, +, or –).
Displays a ﬁ xed sign ($, +, or –) to the left in the cell followed by spaces until the ﬁ rst
signiﬁ cant digit.
Displays the characters in the cell in the designated color. In the example, positive numbers
appear in the default color, and negative numbers appear in red.
“ “ (quotation marks)
$0.00 “Surplus”; $-0.00
Displays text along with numbers entered in a cell.
Skips the width of the character that follows the underscore.
Before creating custom format codes or modifying an existing custom format code, you should understand
their makeup. As shown below, a format code can have up to four sections: positive numbers, negative numbers,
zeros, and text. Each section is divided by a semicolon.
$*#,##0.00; [Blue]#,##0.00; 0.00; “The answer is”@
A format code need not have all four sections. For most applications, a format code will have only a positive
section and possibly a negative section.
The next step is to create and assign a custom format code to the range C6:G11. To assign a custom format
code, you select the Custom category in the Category list in the Format Cells dialog box, select a format code close
to the one to be created, and then modify or customize the selected format code. The following steps create and
assign a custom format code.