This element defines the number formats in this workbook, consisting of a sequence of numFmt records, where each numFmt record defines a particular number format, indicating how to format and render the numeric value of a cell.

[Example:

This cell is formatting as US currency:

The XML expressing this format shows that the formatId is "166" and the decoded formatCode is $#,##0.00

end example]

Number Format Codes

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. To skip a section, the ending semicolon for that section must be written.

The first section, "Format for positive numbers", is the format code that applies to the cell when the cell value contains a positive number.

The second section, "Format for negative numbers", is the format code that applies to the cell when the cell value contains a negative number.

The third section, "Format for zeros", is the format code that applies to the cell when the cell value is zero.

The fourth, and last, section, "Format for text", is the format code that applies to the cell when the cell value is text.

The & (ampersand) text operator is used to join, or concatenate, two values.

The following table describes the different symbols that are available for use in custom number formats.

Format 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.

Text and spacing

Display both text and numbers

To display both text and numbers in a cell, enclose the text characters in double quotation marks (" ") or precede a single character with a backslash (\). Single quotation marks shall not be used to denote text. Characters inside double quotes, or immediately following backslash shall never be interpreted as part of the format code lexicon; instead they shall always be treated as literal strings. Remember to include the characters in the appropriate section of the format codes. For example, type the format $0.00" Surplus";$-0.00" Shortage" to display a positive amount as "$125.74 Surplus" and a negative amount as "$-125.74 Shortage."

The following characters are displayed without the use of quotation marks.

$	Dollar sign	-	Minus sign
+	Plus sign	/	Slash mark
(	Left parenthesis	)	Right parenthesis
:	Colon	!	Exclamation point
^	Circumflex accent (caret)	&	Ampersand
'	Apostrophe	~	Tilde
{	Left curly bracket	}	Right curly bracket
<	Less-than sign	>	Greater-than sign
=	Equal sign		Space character

Include a section for text entry

If included, a text section must be the last section in the number format. Include an "at" sign (@) in the section, precisely where the cell’s text value should be displayed. If the @ character is omitted from the text section, text typed in the cell will not be displayed. To always display specific text characters with the typed text, enclose the additional text in double quotation marks (" "). For example, if “June” is typed into the cell, and the text format is "gross receipts for "@ , then the cell will display “gross receipts for June”.

If the format does not include a text section, text entered in a cell is not affected by the format code.

Add spaces

To create a space that is the width of a character in a number format, include an underscore, followed by the character. For example, when an underscore is followed with a right parenthesis, such as _), positive numbers line up correctly with negative numbers that are enclosed in parentheses because positive numbers are displayed with a blank space after them exactly the width of the right parenthesis character.

Repeat characters

To repeat the next character in the format to fill the column width, include an asterisk (*) in the number format. For example, type 0*- to include enough dashes after a number to fill the cell, or type *0 before any format to include leading zeros.

Decimal places, spaces, colors, and conditions

Include decimal places and significant digits

To format fractions or numbers with decimal points, include the following digit placeholders in a section. If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. If the format contains only number signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point.

# (number sign) displays only significant digits and does not display insignificant zeros.

0 (zero) displays insignificant zeros if a number has fewer digits than there are zeros in the format.

? (question mark) adds spaces for insignificant zeros on either side of the decimal point so that decimal points align when they are formatted with a fixed-width font, such as Courier New. ? can also be used for fractions that have varying numbers of digits.

To display	As	Use this code
1234.59	1234.6	####.#
8.9	8.900	#.000
.631	0.6	0.#
12 1234.568	12.0 1234.57	#.0#
44.398 102.65 2.8	44.398 102.65 2.8 (with aligned decimals)	???.???
5.25 5.3	5 1/4 5 3/10 (with aligned fractions)	# ???/???

Display a thousands separator

To display a comma as a thousands separator or to scale a number by a multiple of 1,000, include a comma in the number format.

To display	As	Use this code
12000	12,000	#,###
12000	12	#,
12200000	12.2	0.0,,

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]	[Blue]	[Cyan]
[Green]	[Magenta]	[Red]
[White]	[Yellow]

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.

[Note: the default legacy color palette values are listed in §3.8.26. In the format codes, [Color1] refers to the color associated with indexed="8", or black (by default), [Color2] refers to the color associated with indexed="9", or white (by default), and so on up to [Color56] referring to the color associated with indexed="63". If the color palette has been customized from default values, then the colors associated with these indexes will reflect those customizations.

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.

Currency, percentages, and scientific notation

Include currency symbols

To include currency symbols, place the currency symbol in the location it should when displayed.

Display percentages

To display numbers as a percentage of 100 — for example, to display .08 as 8% or 2.8 as 280% — include the percent sign (%) in the number format.

Display scientific notations

To display numbers in scientific format, use exponent codes in a section — for example, E-, E+, e-, or e+.

If a format contains a zero (0) or number sign (#) to the right of an exponent code, the application displays the number in scientific format and inserts an "E" or "e". The number of zeros or number signs to the right of a code determines the number of digits in the exponent. "E-" or "e-" places a minus sign by negative exponents. "E+" or "e+" places a minus sign by negative exponents and a plus sign by positive exponents.

Dates and times

Display days, months, and years

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

See §3.17.4.1 for special handling of certain days in the year 1900.

Month versus minutes

If "m" or "mm" code is used immediately after the "h" or "hh" code (for hours) or immediately before the "ss" code (for seconds), the application shall display minutes instead of the month.

Display hours, minutes, and seconds

To display	As	Use this code
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 (hours and minutes)	1:02	[h]:mm
Elapsed time (minutes and seconds)	62:16	[mm]:ss
Elapsed time (seconds and hundredths)	3735.80	[ss].00

Minutes versus month

The "m" or "mm" code must appear immediately after the "h" or "hh" code or immediately before the "ss" code; otherwise, these will display as the month instead of minutes.

AM and PM

If the format contains AM or PM, the hour is based on the 12-hour clock, where "AM" or "A" indicates times from midnight until noon and "PM" or "P" indicates times from noon until midnight. Otherwise, the hour is based on the 24-hour clock.

Invalid date and time values

Cells formatted with a date or time format and which contain invalid date or time values shall show the pound sign ("#") across the width of the cell.

International Considerations

Format Code	Description
r	JPN/CHT Only. When loading in JPN locale, code becomes "ee". When loading in CHT locale, code becomes "e".
rr	JPN/CHT Only. When loading in JPN locale, code becomes "gggee". When loading in CHT locale, code becomes "e".
g	When loading in JPN locale: Single Roman character emperor reign When loading in CHT (Taiwan only) locale: treat same as "gg".
gg	When loading in JPN locale: Single Kanji character emperor reign When loading in CHT locale: Last era short name (since 1911)
ggg	When loading in JPN locale: Tow Kanji character emperor reign When loading in CHT locale: Last era long name (since 1911)
e	When loading in JPN locale: Era year When laoding in CHT (Tawian only) locale: Era year since 1912. If preceeded by “g”, “gg”, or “ggg” then year of 1912, and year before 1912 are special, otherwise years less than 1912 are gregorian. OTHER locales: becomes "yy"
ee	When loading in JPN locale: Era year w/ leading zero When loading in CHT (Tawian only) locale: Era year since 1911 OTHER locales: becomes "yy"
b2	Hijri calander
b1	Gregorian calendar
[$USD-409]	Specifies currency and locale/date system/number system information. Syntax is [$<Currency String>-<language info>]. Currency string is a string to use as a currency symbol. Language info is a 32-bit value entered in hexidecimal format. Language info format (byte 3 is most significant byte): Bytes 0,1: 16-bit Language ID (LID). Byte 2: Calendar type. High bit indicates that input is parsed using specified calendar. Byte 3: Number system type. High bit indicates that input is parsed using specified number system. Special language info values: 0xf800: System long date format 0xf400: System time format

Parent Elements

styleSheet (§3.8.39)

Child Elements	Subclause
numFmt (Number Format)	§3.8.30

Attributes

Description

count (Number Format Count)

Count of number format elements.

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

The following XML Schema fragment defines the contents of this element:

</sequence>

</complexType>