- Select the cells you want to format.
- On the Format menu, click Cells, and then click the Number tab.
- In the Category list, click a category that is similar to the one you want, and then set its built-in formats to be similar to the ones you want. (You will not alter the built-in format; you'll be creating a copy to customize.)
- In the Category list, click Custom.
- In the Type box, edit the number format codes to create the format you want.
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.
Use format codes that describe how you want to display a number; date or time; currency, percentage, or scientific notation; and text or spacing.
Displaying 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: $ - + / ( ) : ! ^ & ' (left single quotation mark) ' (right single quotation mark) ~ { } = < > and the space character.
Including 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 entered in the cell. If the @ character is omitted from the text section, text you enter will not be displayed. If you want to always display specific text characters with the entered text, enclose the additional text in double quotation marks (" ")
— for example, "gross receipts for "@ If the format does not include a text section, text you enter is not affected by the format.
Adding space To create a space 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 closing parenthesis (_)), positive numbers line up correctly with negative numbers that are 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.
Decimal places, spaces, colors, and conditionsDecimal 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 one begin with a decimal point.
- # 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.
- ? adds spaces for insignificant zeros on either side of the decimal point so that decimal points align when formatted with a fixed-width font, such as Courier New. You can also use ? for fractions that have varying numbers of digits.
To display Use this code 1234.59 as 1234.6 ####.# 8.9 as 8.900 #.000 .631 as 0.6 0.# 12 as 12.0 and 1234.568 as 1234.57 #.0# 44.398, 102.65, and 2.8 with aligned decimals ???.??? 5.25 as 5 1/4 and 5.3 as 5 3/10, with aligned division symbols # ???/???
Thousands separator To display a comma as a thousands separator or to scale a number by a multiple of one thousand, include a comma in the number format.To display Use this code 12000 as 12,000 #,### 12000 as 12 #, 12200000 as 12.2 0.0,,
Color 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]
Conditions To set number formats that will be applied only if a number meets a condition you specify, enclose the condition in square brackets. The condition consists of a comparison operator and a value. For example, the following format displays numbers less than or equal to 100 in a red font and numbers greater than 100 in a blue font.[Red][<=100];[Blue][>100]
To apply conditional formats 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 notationCurrency symbols To enter one of the following currency symbols in a number format, turn on NUM LOCK and use the numeric keypad to enter the ANSI code for the symbol.
To enter Hold down ALT and type this code ¢ 0162 £ 0163 ¥ 0165 
0128 Note Custom formats are saved with the workbook. To have Microsoft Excel always use a specific currency symbol, change the currency symbol selected in Regional Settings in Control Panel before you start Excel.
Percentage To display numbers as a percentage of 100, include the percent sign (%) in the number format. For example, a number such as .08 appears as 8%; 2.8 appears as 280%.
Scientific notation To display numbers in scientific format, use "E-," "E+," "e-," or "e+" exponent codes in a section. 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.
Days, months, and years If you use "m" immediately after the "h" or "hh" code or immediately before the "ss" code, Microsoft Excel displays minutes instead of the month.
To display Use this code Months as 1–12 m Months as 01–12 mm Months as Jan–Dec mmm Months as January–December mmmm Months as the first letter of the month mmmmm Days as 1–31 d Days as 01–31 dd Days as Sun–Sat ddd Days as Sunday–Saturday dddd Years as 00–99 yy Years as 1900–9999 yyyy
Hours, minutes, and secondsTo display Use this code Hours as 0–23 H Hours as 00–23 hh Minutes as 0–59 m Minutes as 00–59 mm Seconds as 0–59 s Seconds as 00–59 ss Hours as 4 AM h AM/PM Time as 4:36 PM h:mm AM/PM Time as 4:36:03 P h:mm:ss A/P Elapsed time in hours; for example, 25.02 [h]:mm Elapsed time in minutes; for example, 63:46 [mm]:ss Elapsed time in seconds [ss] Fractions of a second h:mm:ss.00
AM and PM If the format contains an 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. 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.Note You can also use the & (ampersand) text operator to join, or concatenate, two values.
- On the Format menu, click Cells, and then click the Number tab.
- In the Category list, click Custom.
- At the bottom of the Type box, click the custom format you want to delete.
- Click Delete.
Microsoft Excel applies the default General format to any cells in the workbook that were formatted with the deleted custom format.