Guidelines to understand before you customize a number format in Excel 2007

To create a custom number format in Excel , you should first understand the guidelines for custom number formats and start by selecting a built-in number format. You can then change any part of the code in that format to create your own custom number format.

The number format can contain up to four pieces of code, each separated by semicolons. The Code section defines the format of positive, negative, 0, and text in chronological order.

　　< Positive >;< Negative number >;< 0 >;< text >

For example, you can use these code sections to create the following custom formats:

　　[ blue ]#,# #0.00_); [ Red ] (#,# #0. xx); 0.00; " Sales "@

You do not need to include all the code sections in your custom number format. If you specify two code parts for a custom number format only, the first part is for positive numbers and 0, and the second part is for negative numbers. If you specify only one part of the code, that part will be used for all numbers. If you want to skip a portion of the code and then include a portion of the code after it, you must include the end semicolon for the part that you want to skip.

The following guidelines are useful when customizing all of these number format code sections.

Guidelines for containing text and adding spaces

• display text and numbers at the same time to display both text and numbers in a cell, you should enclose characters the text word in double quotation marks ("") or precede a single character with a backslash (\). The characters should be included in the appropriate section of the format code. For example, type the format ￥0.00 " surplus "; ￥ -0.00 " Loss " The positive amount" ￥125.74 surplus "and negative amount" ￥-125.74 loss "can be displayed. Note that there is a space character before "surplus" and "loss" in each code section.

Quotation marks are not used when displaying the following characters.

$ (￥)	Dollar sign (RMB symbol)
+	Plus
(	Opening parenthesis
:	Colon
^	circumflex character (caret)
‘	Apostrophe
{	Left Curly Brace
<	Less than sign
=	equals sign
-	Minus sign
/	Slash symbol
)	Closing parenthesis
!	Exclamation number
&	and number
~	Waveform characters
}	Closing curly Braces
>	Greater than sign
	Space character

• contains the text input part If you include text, the text part is always the last part of the number format. If you want to display any text that you type in a cell, you should include the @ character in that section. If you omit the @ character in the text section, the text you typed is not displayed. If you want to always display specific text characters for the text you type, enclose the additional text in double quotation marks (""). For example," total revenue " @

If the format does not contain a text part, any non-numeric values that you type in the cells to which you apply the formatting are not affected by that format. In addition, the entire cell is converted to text.

• Add a space to create a character-width space in the number format, include an underscore character (_), followed by the character you want to use. For example, if the underscore is followed by a closing parenthesis (such as _), the positive number is aligned accordingly to the negative numbers enclosed in parentheses.
• repeating 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 to contain leading zeros before any formatting.

Guidelines for working with decimal digits, spaces, colors, and conditions

• include decimal places and significant bits to format fractions or numbers that contain decimals, include the following number placeholders, decimal points, and thousands separators in the number format section.

0 (0)	If the number has fewer digits than the format requires, the digit placeholder displays an invalid zero. For example, if you type 8.9 , but you want to display it as 8.90 , use the format #.00 .
#	#.## and you type 8.9 in a cell, the number 8.9 appears.
	0.0 causes the number 8.9 in the column to align with the decimal point of the number 88.99 .

• If the number of digits to the right of the decimal point is greater than the number of placeholders in the format, the number of decimal places is rounded to the same number of placeholders. If the number of digits to the left of the decimal point is greater than the number of placeholders in the format, the extra number of digits is displayed. If the format contains a number sign (#) only to the left of the decimal point, numbers less than 1 start with a decimal point, for example. 47.

Show content	Display format	The code used
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 (decimal point Alignment)	???.???
5.25 5.3	5 1/4 5 3/10 (Score aligned)	# ??? /???

• display thousand separators to display commas as thousands separators or to scale numbers by multiples of 1,000, include the following separators in the number format.

, (comma)

Displays the thousands separator in a number. If the format contains commas with a number sign (#) or zero on either side, Excel separates the thousands with commas. The comma following the digit placeholder scales the number in multiples of 1,000. For example, if the format is #.0, and you type 12,200,000in the cell, the number 12.200.0is displayed.

Show content	Display format	The code used
12000	12,000	#,###
12000	12	#,
12200000	12.2	0.0,,

• Specify a color to specify a color for a portion of the format, type one of the following eight colors (enclosed in square brackets) in the section. The color code must be the first item in the section.

Black

Green

White

Blue

[Magenta]

Yellow

[Blue-green]

Red

• Specify criteria to specify the number format to be applied only if the number meets the criteria you specify, enclose the condition in square brackets. The condition consists of a comparison operator and a value. For example, the following format displays a number that is less than or equal to 100 as a red font, and a number greater than 100 as a blue font.

　　[ red ][<=100]; [ Blue ][>100]

To apply conditional formatting to cells (for example, color shading that varies with cell values), on the start tab, in the styles Group, click Conditional Formatting .

Guidelines for Fagor on currencies, percentages and scientific notation

• include currency symbol to type one of the following currency symbols in the number format, press Num Lock and use the numeric keypad to type the ANSI code for the symbol.

show content td>	code
¢	alt+0162
£	alt+0163
¥	alt+0165
	alt+0128

• Note The custom format is saved with the workbook. To make Excel always use a specific currency symbol, you must change the currency symbol that is selected in Regional Options in Control Panel before you start Excel.
• Show percentages to display a number as a percentage (for example, to display. 08 as 8% or 2.8 for 280%), include the percent sign (%) in the number format.
• Show scientific notation to display numbers in the scientific notation (exponential) format, use the following exponential code in the number format section.

E, e+, E-, e+)

Displays numbers in scientific notation (exponential) format. Excel displays a number that corresponds to the number of digits that the decimal point moves on the right side of "E" or "E". For example, if the format is 0.00E+00and you type 12,200,000in the cell, the number 1.22E+07is displayed. If you change the number format to #0.0E+0, the number 12.2E+6is displayed.

Guidelines for date and time formats

• show days, months, and years to display numbers as date formats, such as day, month, and year, use the following code in the number format section.

M	Displays the month as a number without leading zeros.
Mm	Displays the month as a number with leading zeros as needed.
Mmm	Displays the month as an abbreviated form (Jan to Dec).
Mmmm	Displays the month as the full name (January through December).
Mmmmm	Displays the month as a single letter (J to D).
D	Displays the day as a number without leading zeros.
Dd	Displays the day as a number with leading zeros as needed.
Ddd	Displays the day as an abbreviated form (Sun to Sat).
dddd	Displays the day as the full name (Sunday through Saturday).
Yy	Displays the year as a two-digit number.
yyyy	Displays the year as a four-digit number.

Show content	Display format	The code used
Month	1-12	M
Month	01–12	Mm
Month	Jan–dec	Mmm
Month	January–december	Mmmm
Month	J–d	Mmmmm
Day	1-31	D
Day	01–31	Dd
Day	Sun–sat	Ddd
Day	Sunday–saturday	dddd
Years	00–99	Yy
Years	1900-9999	yyyy

• display hours, minutes, and seconds to display the time format, such as hours, minutes, and seconds, use the following code in the number format section.

H	Displays the hour as a number without leading zeros.
[h]	Displays the elapsed time in hours. If you use a formula that returns a time of more than 24 hours, use a number format similar to [H]:mm:ss .
hh	Displays the hour as a number with leading zeros as needed. If the format contains AM or PM, it is a 12-hour system, otherwise 24-hour.
M	Displays the minute as a number without leading zeros. 　　 Note the m or mm code must immediately follow the H or hh code or must follow SS
[M]	Displays the elapsed time in minutes. If the formula you are using returns more than 60 minutes, use a number format similar to [Mm]:ss .
Mm	Displays the minute as a number with leading zeros as needed. 　　 Note the m or mm code must immediately follow the H or HH code or must be followed by the SS code; , Excel displays the month instead of the minute.
S	Displays the seconds as a number without leading zeros.
[S]	Displays the elapsed time in seconds. If the formula you are using returns more than 60 seconds, use a number format similar to [SS] .
Ss	Displays the seconds as a number with leading zeros as needed. If you want to display the decimal portion of a second, use a number format similar to h:mm:ss.00 .
AM/PM, AM/PM, a/p, a/p	Displays hours using the 12-hour system. Excel Displays am,am,a , or afor hours from midnight to noon, and displays pm,pm, P for the time from noon to midnight or P.

Show content	Display format	The code used
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 1% seconds)	3735.80	[ss].00

Guidelines to understand before you customize a number format in Excel 2007