Show All

Hide All

If a built-in number format does not meet your needs, you can create a new number format that is based on an existing number format and add it to the list of custom number formats.

Note  Custom number formats are stored in the active workbook and are not available to new workbooks that you open. If you want custom formats to be available in a new workbook, you can create a template.

1. Open the workbook in which you want to store the custom format.
2. On the Format menu, click Cells.
3. On the Number tab, click Custom in the Category list.
4. In the Type list, select the number format that you want to edit or delete.

   The number format that you select appears in the Type box above the Type list.

   Note  When you select an existing number format in the Type list, Excel keeps the original number format and creates a copy of it that you can customize. The original number format in the Type list cannot be altered or deleted.

5. Do one of the following:
   • To create a new number format, edit the selected number format in the Type box.

     Guidelines for number format codes

     You can specify up to four sections of format codes. The format codes, separated by semicolons, define the formats for positive numbers, negative numbers, zero values, and text, in that order. If you specify only two sections, the first is used for positive numbers and zeros, and the second is used for negative numbers. If you specify only one section, it is used for all numbers. If you skip a section, include the ending semicolon for that section.

     

     You can also use the & (ampersand) text operator (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.) to join, or concatenate, two values.

     The following number format code guidelines may be helpful when you create a custom number format.

     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 (\). 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 is always the last section in the number format. Include an "at" sign (@) in the section where you want to display any text typed in the cell. If the @ character is omitted from the text section, text that you type will not be displayed. If you want to always display specific text characters with the typed text, enclose the additional text in double quotation marks (" "). For example, "gross receipts for "@

     If the format does not include a text section, text that you type is not affected by the format.

     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 you follow an underscore with a right parenthesis, such as _), positive numbers line up correctly with negative numbers that are enclosed in parentheses.

     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. You can also use ? 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 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]

     
     

     Specify conditions

     To set number formats that will be applied only if a number meets a condition that you specify, enclose the condition in square brackets. The condition consists of a comparison operator (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.) and a value. 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]

     To apply conditional formats (conditional format: A format, such as cell shading or font color, that Excel automatically applies to cells if a specified condition is true.) to cells — for example, color shading that depends on the value of a cell — use the Conditional Formatting command on the Format menu.

     Currency, percentages, and scientific notation

     Include currency symbols

     To type one of the following currency symbols in a number format, press NUM LOCK and use the numeric keypad to type the ANSI code for the symbol.

     To enter	Press this code
     ¢	ALT+0162
     £	ALT+0163
     ¥	ALT+0165
     	ALT+0128

     Note  Custom formats are saved with the workbook. To have Microsoft Excel always use a specific currency symbol, you need to change the currency symbol that is selected in the regional settings in Windows Control Panel before you start Excel. For information about how to change the regional settings, see Change the default country/region.

     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, Excel 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

     Month versus minutes    If you use the "m" or "mm" code immediately after the "h" or "hh" code (for hours) or immediately before the "ss" code (for seconds), Excel displays 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, Microsoft Excel displays 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.  
   • To delete the number format, click Delete.

     Microsoft Excel applies the default General format to any cells in the workbook that were formatted with the deleted custom format.