Creating Custom Number Formats

If none of the built-in number formats has what you need, you can define custom number formats. Each custom number format can have as many as four sections: one for positive numbers, one for negative numbers, one for zeros, and one for text. Each section is optional; the sections are separated by semicolons. The following example shows a custom number format.

#,###;(#,###);0;"Error: Entry must be numeric"

To define a custom number format:

Select the cells for which you want to create the custom number format.
Select Format > Cells and select the Number tab, if necessary.
Select a category for the custom number format from the Category list.
In the Number Format text box, type a custom number format built from the custom number format characters described below.
Click OK.

Custom Number Format Symbols

The following table lists the number format symbols you can use in a custom number format string.

Format Symbol Description

General Displays the number in General format.

0 Digit placeholder. If the number contains fewer digits than the format contains placeholders, the number is padded with 0's. If there are more digits to the right of the decimal than there are placeholders, the decimal portion is rounded to the number of places specified by the placeholders. If there are more digits to the left of the decimal than there are placeholders, the extra digits are retained.

# Digit placeholder. This placeholder functions the same as the 0 placeholder except the number is not padded with 0's if the number contains fewer digits than the format contains placeholders.

? Digit placeholder. This placeholder functions the same as the 0 placeholder except that spaces are used to pad the digits.

. (period) Decimal point. Determines how many digits (0's or #'s) are displayed on either side of the decimal point. If the format contains only #'s left of the decimal point, numbers less than 1 begin with a decimal point. If the format contains 0's left of the decimal point, numbers less than 1 begin with a 0 left of the decimal point.

% Displays the number as a percentage. The number is multiplied by 100 and the % character is appended.

, (comma) Thousands separator. If the format contains commas separated by #'s or 0's, the number is displayed with commas separating thousands. A comma following a placeholder scales the number by a thousand. For example, the format 0 scales the number by 1000 (10,000 would be displayed as 10).

E- E+ e- e+ Displays the number as scientific notation. If the format contains a scientific notation symbol to the left of a 0 or # placeholder, the number is displayed in scientific notation and an E or an e is added. The number of 0 and # placeholders to the right of the decimal determines the number of digits in the exponent. E- and e- place a minus sign by negative exponents. E+ and e+ place a minus sign by negative exponents and a plus sign by positive exponents.

$ - + / ( ) : space Displays that character. To display a character other than those listed, precede the character with a back slash (\) or enclose the character in double quotation marks (" "). You can also use the slash (/) for fraction formats.

\ Displays the next character. The backslash is not displayed. You can also display a character or string of characters by surrounding the characters with double quotation marks (" ").

The backslash is inserted automatically for the following characters:! ^ & ` (left quote) '(right quote) ~ { } = < >

* (asterisk) Repeats the next character until the width of the column is filled. You cannot have more than one asterisk in each format section.

_ (underline) Skips the width of the next character. For example, to make negative numbers surrounded by parentheses align with positive numbers, you can include the format _) after positive numbers to skip the width of the parenthesis.

"text" Displays the text inside the quotation marks.

@ Text placeholder. If the cell contains text, the text replaces the @ format character.

m Month number. Displays the month as digits without leading zeros (e.g., 1-12). When used with h or hh formats, represents minutes.

mm Month number. Displays the month as digits with leading zeros (e.g., 01-12). When used with the h or hh formats, represents minutes.

mmm Month abbreviation. Displays the month as a three-letter abbreviation (e.g., Jan-Dec).

mmmm Month name. Displays the month as a full name (e.g., January-December).

mmmmm Month abbreviation. Displays the first letter of the month's name (e.g., J-D).

d Day number. Displays the day as digits with no leading zero (e.g., 1-2).

dd Day number. Displays the day as digits with leading zeros (e.g., 01-02).

ddd Day abbreviation. Displays the day as an abbreviation (e.g., Sun-Sat).

dddd Day name. Displays the day as a full name (e.g., Sunday-Saturday).

yy Year number. Displays the year as a two-digit number (e.g., 00-99).

yyyy Year number. Displays the year as a four-digit number (e.g., 1900-2078).

g If you are using a Japanese locale, this displays the era symbol as a Latin letter.

gg If you are using a Japanese locale, this displays the first character of an era name.

ggg If you are using a Japanese locale, this displays the full era name.

e If you are using a Japanese locale, this displays the full era year.

ee If you are using a Japanese locale, this displays the full era year with a leading 0.

h Hour number. Displays the hour as a number without leading zeros (e.g., 1-23). If the format contains one of the AM or PM formats, the hour is based on a 12-hour clock. Otherwise, it is based on a 24-hour clock.

hh Hour number. Displays the hour as a number with leading zeros (e.g., 01-23). If the format contains one of the AM or PM formats, the hour is based on a 12-hour clock. Otherwise, it is based on a 24-hour clock.

m Minute number. Displays the minute as a number without leading zeros (e.g., 0-59) when it appears immediately after the h or hh symbol. Otherwise, it is interpreted as a month number.

mm Minute number. Displays the minute as a number with leading zeros (e.g., 00-59) when it appears immediately after the h or hh symbol. Otherwise, it is interpreted as a month number.

s Second number without leading zeros (e.g., 0-59).

ss Second number with leading zeros (e.g., 00-59).

AM/PM, am/pm, A/P, a/p 12-hour time. Displays time using a 12-hour clock. Displays AM, am, A, or a for times between midnight and noon; displays PM, pm, P, or p for times between noon and midnight.

[h] Total number of hours.

[m] Total number of minutes.

[s] Total number of seconds.

s.0, s.00, s.000 Second number, including fractional part, without leading zeros.

ss.0, ss.00, ss.000 Second number, including fractional part, with leading zeros.

[Black] Displays cell text in black.

[Blue] Displays cell text in blue.

[Cyan] Displays cell text in cyan.

[Green] Displays cell text in green.

[Magenta] Displays cell text in magenta.

[Red] Displays cell text in red.

[White] Displays cell text in white.

[Yellow] Displays cell text in yellow.

[Colorn] Displays cell text using the corresponding color in the color palette. n is a color in the color palette.

[conditional value] Using the conditional value brackets [ ], you can designate a different condition for each of the four format sections (positive numbers, negative numbers, zeros, and text). For example, you might want positive numbers displayed in black, negative numbers in red, and zeros in blue. The following string formats a number for these conditions:[>0][Black]General; [<0][Red]General; [Blue]General

Format Symbol	Description
General	Displays the number in General format.
0	Digit placeholder. If the number contains fewer digits than the format contains placeholders, the number is padded with 0's. If there are more digits to the right of the decimal than there are placeholders, the decimal portion is rounded to the number of places specified by the placeholders. If there are more digits to the left of the decimal than there are placeholders, the extra digits are retained.
#	Digit placeholder. This placeholder functions the same as the 0 placeholder except the number is not padded with 0's if the number contains fewer digits than the format contains placeholders.
?	Digit placeholder. This placeholder functions the same as the 0 placeholder except that spaces are used to pad the digits.
. (period)	Decimal point. Determines how many digits (0's or #'s) are displayed on either side of the decimal point. If the format contains only #'s left of the decimal point, numbers less than 1 begin with a decimal point. If the format contains 0's left of the decimal point, numbers less than 1 begin with a 0 left of the decimal point.
%	Displays the number as a percentage. The number is multiplied by 100 and the % character is appended.
, (comma)	Thousands separator. If the format contains commas separated by #'s or 0's, the number is displayed with commas separating thousands. A comma following a placeholder scales the number by a thousand. For example, the format 0 scales the number by 1000 (10,000 would be displayed as 10).
E- E+ e- e+	Displays the number as scientific notation. If the format contains a scientific notation symbol to the left of a 0 or # placeholder, the number is displayed in scientific notation and an E or an e is added. The number of 0 and # placeholders to the right of the decimal determines the number of digits in the exponent. E- and e- place a minus sign by negative exponents. E+ and e+ place a minus sign by negative exponents and a plus sign by positive exponents.
$ - + / ( ) : space	Displays that character. To display a character other than those listed, precede the character with a back slash (\) or enclose the character in double quotation marks (" "). You can also use the slash (/) for fraction formats.
\	Displays the next character. The backslash is not displayed. You can also display a character or string of characters by surrounding the characters with double quotation marks (" ").
	The backslash is inserted automatically for the following characters:! ^ & ` (left quote) '(right quote) ~ { } = < >
* (asterisk)	Repeats the next character until the width of the column is filled. You cannot have more than one asterisk in each format section.
_ (underline)	Skips the width of the next character. For example, to make negative numbers surrounded by parentheses align with positive numbers, you can include the format _) after positive numbers to skip the width of the parenthesis.
"text"	Displays the text inside the quotation marks.
@	Text placeholder. If the cell contains text, the text replaces the @ format character.
m	Month number. Displays the month as digits without leading zeros (e.g., 1-12). When used with h or hh formats, represents minutes.
mm	Month number. Displays the month as digits with leading zeros (e.g., 01-12). When used with the h or hh formats, represents minutes.
mmm	Month abbreviation. Displays the month as a three-letter abbreviation (e.g., Jan-Dec).
mmmm	Month name. Displays the month as a full name (e.g., January-December).
mmmmm	Month abbreviation. Displays the first letter of the month's name (e.g., J-D).
d	Day number. Displays the day as digits with no leading zero (e.g., 1-2).
dd	Day number. Displays the day as digits with leading zeros (e.g., 01-02).
ddd	Day abbreviation. Displays the day as an abbreviation (e.g., Sun-Sat).
dddd	Day name. Displays the day as a full name (e.g., Sunday-Saturday).
yy	Year number. Displays the year as a two-digit number (e.g., 00-99).
yyyy	Year number. Displays the year as a four-digit number (e.g., 1900-2078).
g	If you are using a Japanese locale, this displays the era symbol as a Latin letter.
gg	If you are using a Japanese locale, this displays the first character of an era name.
ggg	If you are using a Japanese locale, this displays the full era name.
e	If you are using a Japanese locale, this displays the full era year.
ee	If you are using a Japanese locale, this displays the full era year with a leading 0.
h	Hour number. Displays the hour as a number without leading zeros (e.g., 1-23). If the format contains one of the AM or PM formats, the hour is based on a 12-hour clock. Otherwise, it is based on a 24-hour clock.
hh	Hour number. Displays the hour as a number with leading zeros (e.g., 01-23). If the format contains one of the AM or PM formats, the hour is based on a 12-hour clock. Otherwise, it is based on a 24-hour clock.
m	Minute number. Displays the minute as a number without leading zeros (e.g., 0-59) when it appears immediately after the h or hh symbol. Otherwise, it is interpreted as a month number.
mm	Minute number. Displays the minute as a number with leading zeros (e.g., 00-59) when it appears immediately after the h or hh symbol. Otherwise, it is interpreted as a month number.
s	Second number without leading zeros (e.g., 0-59).
ss	Second number with leading zeros (e.g., 00-59).
AM/PM, am/pm, A/P, a/p	12-hour time. Displays time using a 12-hour clock. Displays AM, am, A, or a for times between midnight and noon; displays PM, pm, P, or p for times between noon and midnight.
[h]	Total number of hours.
[m]	Total number of minutes.
[s]	Total number of seconds.
s.0, s.00, s.000	Second number, including fractional part, without leading zeros.
ss.0, ss.00, ss.000	Second number, including fractional part, with leading zeros.
[Black]	Displays cell text in black.
[Blue]	Displays cell text in blue.
[Cyan]	Displays cell text in cyan.
[Green]	Displays cell text in green.
[Magenta]	Displays cell text in magenta.
[Red]	Displays cell text in red.
[White]	Displays cell text in white.
[Yellow]	Displays cell text in yellow.
[Colorn]	Displays cell text using the corresponding color in the color palette. n is a color in the color palette.
[conditional value]	Using the conditional value brackets [ ], you can designate a different condition for each of the four format sections (positive numbers, negative numbers, zeros, and text). For example, you might want positive numbers displayed in black, negative numbers in red, and zeros in blue. The following string formats a number for these conditions:`[>0][Black]General; [<0][Red]General; [Blue]General`

Example Custom Number Formats

The following table shows some examples of custom number formats and numbers displayed using the custom number formats.

Format Cell Data Display

#.## 123.456 123.46

0.2 .2

#.0# 123.456 123.46

123 123.0

#,##0"CR";#,##0"DR";0 1234.567 1,235CR

0 0

-123.45 123DR

#, 10000 10

"Sales="0.0 123.45 Sales=123.5

-123.45 -Sales=123.5

"X="0.0;"x="-0.0 -12.34 x=-12.3

$* #,##0.00;$* -#,##0.00 1234.567 $ 1,234.57

-12.34 $ -12.34

000-00-0000 123456789 123-45-6789

"Cust. No." 0000 1234 Cust. No. 1234

;;; Anything (Not Displayed)

"The End" 123.45 The End

-123.45 -The End

text text

m-d-yy 2/3/94 2-3-94

mm dd yy 2/3/94 02 03 94

mmm d, yy 2/3/94 Feb 3, 94

mmmm d, yyyy 2/3/94 February 3, 1994

d mmmm yyyy 2/3/94 3 February 1994

hh"h" mm"m" 1:32 AM 01h 32m

h.mm AM/PM 14:56 2.56 PM

hhmm "hours" 3:15 0315 hours

Format	Cell Data	Display
`#.##`	123.456	123.46
`#.##`	0.2	.2
`#.0#`	123.456	123.46
`#.0#`	123	123.0
`#,##0"CR";#,##0"DR";0`	1234.567	1,235CR
	0	0
	-123.45	123DR
`#,`	10000	10
`"Sales="0.0`	123.45	Sales=123.5
`"Sales="0.0`	-123.45	-Sales=123.5
`"X="0.0;"x="-0.0`	-12.34	x=-12.3
`$* #,##0.00;$* -#,##0.00`	1234.567	$ 1,234.57
`$* #,##0.00;$* -#,##0.00`	-12.34	$ -12.34
`000-00-0000`	123456789	123-45-6789
`"Cust. No." 0000`	1234	Cust. No. 1234
`;;;`	Anything	(Not Displayed)
`"The End"`	123.45	The End
	-123.45	-The End
	text	text
`m-d-yy`	2/3/94	2-3-94
`mm dd yy`	2/3/94	02 03 94
`mmm d, yy`	2/3/94	Feb 3, 94
`mmmm d, yyyy`	2/3/94	February 3, 1994
`d mmmm yyyy`	2/3/94	3 February 1994
`hh"h" mm"m"`	1:32 AM	01h 32m
`h.mm AM/PM`	14:56	2.56 PM
`hhmm "hours"`	3:15	0315 hours