Custom format string guidelines
Up to four sections of format codes can be specified. The format codes, separated by semicolons, define the formats for positive numbers, negative numbers, zero values, and text, in that order. If only two sections are specified, the first is used for positive numbers and zeros, and the second is used for negative numbers. If only one section is specified, it is used for all numbers. Four sections example:
#,###.00_);[Red](#,###.00);0.00;"sales "@
The following table describes the different symbols that are available for use in custom number formats.
|
Symbol |
Description and result |
|
0 |
Digit placeholder. For example, if the value 8.9 is to be displayed as 8.90, use the format #.00 |
|
# |
Digit placeholder. This symbol follows the same rules as the 0 symbol. However, the application shall not display extra zeros when the number typed has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.##, and 8.9 is in the cell, the number 8.9 is displayed. |
|
? |
Digit placeholder. This symbol follows the same rules as the 0 symbol. However, the application shall put a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, the custom format 0.0? aligns the decimal points for the numbers 8.9 and 88.99 in a column. |
|
. (period) |
Decimal point. |
|
% |
Percentage. If the cell contains a number between 0 and 1, and the custom format 0% is used, the application shall multiply the number by 100 and adds the percentage symbol in the cell. |
|
, (comma) |
Thousands separator. The application shall separate thousands by commas if the format contains a comma that is enclosed by number signs (#) or by zeros. A comma that follows a placeholder scales the number by one thousand. For example, if the format is #.0,, and the cell value is 12,200,000 then the number 12.2 is displayed. |
|
E- E+ e- e+ |
Scientific format. The application shall display a number to the right of the "E" symbol that corresponds to the number of places that the decimal point was moved. For example, if the format is 0.00E+00, and the value 12,200,000 is in the cell, the number 1.22E+07 is displayed. If the number format is #0.0E+0, then the number 12.2E+6 is displayed. |
|
$-+/():space |
Displays the symbol. If it is desired to display a character that differs from one of these symbols, precede the character with a backslash (\). Alternatively, enclose the character in quotation marks. For example, if the number format is (000), and the value 12 is in the cell, the number (012) is displayed. |
|
\ |
Display the next character in the format. The application shall not display the backslash. For example, if the number format is 0\!, and the value 3 is in the cell, the value 3! is displayed. |
|
* |
Repeat the next character in the format enough times to fill the column to its current width. There shall not be more than one asterisk in one section of the format. If more than one asterisk appears in one section of the format, all but the last asterisk shall be ignored. For example, if the number format is 0*x, and the value 3 is in the cell, the value 3xxxxxx is displayed. The number of x characters that are displayed in the cell varies based on the width of the column. |
|
_ (underline) |
Skip the width of the next character. This is useful for lining up negative and positive values in different cells of the same column. For example, the number format _(0.0_);(0.0) aligns the numbers 2.3 and -4.5 in the column even though the negative number is enclosed by parentheses. |
|
"text" |
Display whatever text is inside the quotation marks. For example, the format 0.00 "dollars" displays 1.23 dollars when the value 1.23 is in the cell. |
|
@ |
Text placeholder. If text is typed in the cell, the text from the cell is placed in the format where the at symbol (@) appears. For example, if the number format is "Bob "@" Smith" (including quotation marks), and the value "John" is in the cell, the value Bob John Smith is displayed. |
|
0 |
Digit placeholder. For example, if the value 8.9 is to be displayed as 8.90, use the format #.00 |
Specify colors
To set the text color for a section of the format, type the name of one of the following eight colors in square brackets in the section. The color code must be the first item in the section.
|
[Black] |
[Green] |
[White] |
[Blue] |
|
[Magenta] |
[Yellow] |
[Cyan] |
[Red] |
Instead of using the name of the color, the color index can be used, like this [Color3] for Red. Valid numeric indexes for color range from 1 to 56, which reference by index to the legacy color palette.
Specify conditions
To set number formats that will be applied only if a number meets a specified condition, enclose the condition in square brackets. The condition consists of a comparison operator and a value. Comparison operators include: = Equal to; > Greater than; < Less than; >= Greater than or equal to, <= Less than or equal to, and <> Not equal to. For example, the following format displays numbers that are less than or equal to 100 in a red font and numbers that are greater than 100 in a blue font.
[Red][<=100];[Blue][>100]
If the cell value does not meet any of the criteria, then pound signs ("#") are displayed across the width of the cell.
Dates and times
|
To Display |
As |
Use This Code |
|
Months |
1-12 |
m |
|
Months |
01-12 |
mm |
|
Months |
Jan-Dec |
mmm |
|
Months |
January-December |
mmmm |
|
Months |
J-D |
mmmmm |
|
Days |
1-31 |
d |
|
Days |
01-31 |
dd |
|
Days |
Sun-Sat |
ddd |
|
Days |
Sunday-Saturday |
dddd |
|
Years |
00-99 |
yy |
|
Years |
1900-9999 |
yyyy |
|
Hours |
0-23 |
h |
|
Hours |
00-23 |
hh |
|
Minutes |
0-59 |
m |
|
Minutes |
00-59 |
mm |
|
Seconds |
0-59 |
s |
|
Seconds |
00-59 |
ss |
|
Time |
4 AM |
h AM/PM |
|
Time |
4:36 PM |
h:mm AM/PM |
|
Time |
4:36:03 P |
h:mm:ss A/P |
|
Time |
4:36:03.75 |
h:mm:ss.00 |
|
Elapsed time |
1:02 |
[h]:mm |
|
Elapsed time |
62:16 |
[mm]:ss |
|
Elapsed time |
3735.80 |
[ss].00 |
Examples
|
To Display |
As |
Use This Code |
|
1234.59 |
1234.6 |
####.# |
|
8.9 |
8.900 |
#.000 |
|
.631 |
0.6 |
0.# |
|
12 |
12.0 |
#.0# |
|
44.398 |
44.398 |
???.??? |
|
5.25 |
5 1/4 |
# ??/?? |
|
12000 |
12,000 |
#,### |
|
12000 |
12 |
#, |
|
12400000 |
12.4 |
0.0,, |