Custom number formats Using number format codes, you can create your own custom number formats. Number format codes can have up to four sections, separated by semicolons. These sections define the formats for positive numbers, negative numbers, zero values, and text, in that order. If you include only two sections, the first one is used for positive numbers and zeros, and the second one is used for negative numbers. If you have only one section, all numbers use that format. If you skip a section, include the semicolon. For more information about basic number format codes, click . For more information about number format codes for dates and times, click . For more information about number format codes for text and spacing, click . For more information about other number format codes, click . ------------------------------------------ Basic number format codes · Use the General format to display numbers as integers, fractions as decimals, and scientific format for numbers that are too wide for the cell. Click Cells on the Format menu, click the Number tab, and click General in the Type box. · To display a specific number of digits (0's or #'s) to the right and left of the decimal point, include a decimal point in the number format. If the format contains only #'s to the left of the decimal point, numbers less than 1 begin with a decimal point. · To create placeholders for numbers, include the following format codes in a section. If the number has more digits to the right of the decimal point than there are placeholders in the format, the number is rounded to as many decimal places as there are placeholders. If the number has more digits to the left of the decimal point than there are placeholders in the format, the extra digits are displayed. · # does not display extra zeros. · 0 (zero) displays extra zeros when a number has fewer digits than there are zeros in the format. · ? displays extra zeros when a number has fewer digits than there are zeros in the format, and also adds a space for insignificant zeros on either side of the decimal point so that decimal points align. You can also use this symbol for fractions that have varying numbers of digits. To display Use this format code 1234.59 as 1234.6 ####.# 8.9 as 8.900 #.000 12 as 12.0 and 1234.568 as 1234.57 #.0# 5.25 as 5 1/4 and 5.3 as 5 3/10, with aligned division symbols # ???/??? .5 as 0.5 0.## · To display a comma as a thousands separator or to scale a number by a multiple of a thousand, include a comma in the number format. To display Use this format code 12000 as 12,000 #,### 12000 as 12 #, 12200000 as 12.2 0.0,, · To set the color for a section of the format, type the name of the color in square brackets in the section. To display a color from the color palette, include [COLOR n] in a section, where n is a number from 0 to 56. [BLACK] [BLUE] [CYAN] [GREEN] [MAGENTA] [RED] [WHITE] [YELLOW] ------------------------------ Number format codes for dates and times · To display days, months, and years, include the following format codes in a section. If you use m immediately after the h or hh format code, Microsoft Excel displays the minute instead of the month. To display Use this format code Months as 1-12 m Months as 01-12 mm Months as Jan-Dec mmm Months as January-December mmmm 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-2078 yyyy · To display hours, minutes, and seconds, include the following format codes in a section. To display Use this format 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 Time as 4:36 pm h:mm pm Time as 4:36:03 p h:mm:ss p Hours as 25.02 [h]:mm Minutes as 63:46 [mm]:ss If the format contains an AM or PM, the hour is based on the 12-hour clock, where AM, am, A, or a indicates times from midnight until noon, and PM, pm, P, or p indicates times from noon until midnight. Otherwise, the hour is based on the 24-hour clock. The m or mm must appear immediately after the h or hh format code, or Microsoft Excel displays the month rather than the minute. You can also use the decimal point to create time formats that display fractions of a second. To display hours greater than 24, or minutes or seconds greater than 60, place brackets around the leftmost part of the time code. For example, the time code [h]:mm:ss allows the display of hours greater than 24. ----------------------------- Number format codes for text and spacing · To display characters, either enclose the characters in double quotation marks or precede them with a backslash. The following special characters do not need to be enclosed in quotation marks to be displayed: $ - + / ( ) : space. If you enter any of the following symbols, a backslash is provided for you: ! ^ & ` (left single quotation mark) ' (right single quotation mark) ~ { } = < >. · To create a space the width of a character in a number format, include an underscore followed by the character. For example, an underscore followed by a closing parenthesis allows positive numbers to line up correctly with negative numbers that are enclosed in parentheses. · To include a text section in a number format, include the symbol @ in the number format. Any text entered in the cell is formatted according to the section where the @ symbol appears. If the format does not have a text section, text you enter is not affected by the format. ------------------------------- Other number format codes · To display numbers as a percentage, where the number is multiplied by 100 and the percent sign is added, include the percent sign (%) in the number format. · To display numbers in scientific format, include the following format codes in a section. If a format contains a 0 or # to the right of an E-, E+, e-, or e+, Microsoft Excel displays the number in scientific format and inserts an E or e. The number of 0's or #'s to the right determines the exponent's number of digits. 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. · To repeat the next character in the format enough times to fill the column width, include an asterisk (*) in the number format. · To set conditional values for a section, enclose the condition in square brackets, where condition is <, >, =, >=, <=, or <>. You can set conditions for each section of a number format. For example, the following format displays entries greater than 1000 in blue: [>1000][Blue]#,##