Creating Custom Number Formats
The easiest way to create a custom number format is to format a sample number with the built-in number format whose attributes most closely match the ones you want in the new custom format. Continue by opening the Format Cells dialog box (Ctrl+1), selecting the Custom category on the Number tab to display the built-in format's codes, and then building the custom number format by editing its number format codes, as necessary. Excel's number formats can consist of up to four sections:
- First section specifies how to format positive numbers
- Second section specifies how to format negative numbers
- Third section specifies how to format zeros
- Fourth section specifies how to format text
Each part of the format is separated by semicolons (;). If a particular format doesn't specify formatting for negative numbers or zeros, Excel formats them like it does positive numbers. If the format doesn't specify what to do with text, text is formatted as it normally is with the default, Normal format. In the next section table shows the codes you'd use in the various sections of the custom number format you're creating.
| Code | What It Signifies |
| # | Digit placeholder that displays only significant digits and does not display insignificant zeros so that ####.# displays 2450.48 as 2450.5 in the cell. |
| 0 (zero) | Digit placeholder that displays insignificant zeros if a number has fewer digits than the number of places in the format so that the code #.00 displays 4.5 as 4.50 in the cell. |
| ? | Digit placeholder that adds spaces for insignificant zeros on either side of the decimal point to align numbers on the decimal point so the code 0.?? ensures that 10.5 and 12.75 line up because the program pads 10.5 with an extra space. |
| \ (backslash) | Displays the character that immediately follows as text. |
| * (asterisk) | Repeats the character that immediately follows to fill the column width. |
| "text" | Displays whatever text you enclose in the double quotes as text. Note that the following characters do not have to be enclosed in quotes: $, -, +, /, (, ), :, !, ^, &, ' (open single quote), ' (close single quote), ~, {, }, =, <, >, and a space. |
| @ | Text placeholder. By itself, it converts whatever number is entered in the cell into a text entry. When followed by text enclosed in quotation marks, it appends that text onto the number so that the format code @" estimate" appends the word estimate to each number. |
| % | Percentage that adds a percent sign at the end of the number to indicate that its value is a multiple of a hundred. |
| . (period) | Decimal point. |
| , (comma) | Thousands separator. |
| ( ) | Enclosing the code for the negative numbers in the second section of the format displays negative numbers in parentheses as (4.50) instead of -4.50. Add a hyphen (-) to negative fo rmat code to have a negative number preceded by a minus sign. |
| E-, E+, e-, e+ | Converts number into scientific notation. |
| _ (underscore) | Inserts space equal in width to the character that immediately follows, as in _) to pad values with a space equal to the close parenthesis. |
| m | When not following the h or hh code, inserts the month as a number without leading zeros, as in 7-4-05. |
| mm | Inserts the month as a number with leading zeros, as in 07-4-05. |
| mmm | Inserts the month as a three-letter abbreviation, as in Jan, Feb, Mar, and so on. |
| mmmm | Inserts the full name of the month, as in January, February, March, and so on. |
| d | Inserts the date as a number without leading zeros, as in 4-1-04. |
| dd | Inserts the date as a number with leading zeros, as in 4-02-04. |
| ddd | Inserts the day of the week with a three-letter abbreviation, as in Mon, Tue, or Wed. |
| dddd | Inserts the full day of the week, as in Monday, Tuesday, or Wednesday. |
| yy | Inserts the last two digits of the year, as in 2-15-05. |
| yyyy | Inserts all four digits of the year, as in 2-15-2005. |
| h | Inserts the number of the hour without leading zeros, as in 9:15. |
| hh | Inserts the number of the hour with leading zeros, as in 09:15. |
| m | When immediately following the h or hh code, inserts the number of minutes without leading zeros (0-59), as in 5:5. |
| mm | Inserts the number of the minutes with leading zeros (00-59), as in 5:05. |
| s | Inserts the number of seconds without leading zeros (0-59), as in 5:05:7. |
| ss | Inserts the number of seconds with leading zeros (00-59), as in 5:05:07. For fractions of a second, use a format that includes fractions such as h:mm:ss.00. |
| [ ] | Displays hours greater than 24 and minutes or seconds greater than 60. |
| AM/PM | Inserts AM or PM after time numbers depending upon whether the time is before or after noon, as in 10:15 PM. (Otherwise Excel formats the time using a 24-hour clock as in 22:15.) |
| [Color] | Specifies the font color for the section of the format as specified by the color name, as in [BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], or [YELLOW]. Note that [Color] must precede the other codes in the section, as in [RED](#,##0) in the negative section, to have negative values displayed in red and enclosed in parentheses. |
| [Colorn] | Specifies a font color for the section of the format as specified by a number (n) between 1 and 56. Note that this [Colorn] code must precede all other code in the section. |
When defining custom formats for currency, you enter the currency signifier, as in $ for dollars, £ (Alt+0163) for pounds, ¥ (Alt+0165) for yen, and € (Alt+0128) for euros. Use a comma (,) to indicate the presence of the thousands separator and a period (.) to indicate the decimal point. When defining custom date formats, you can separate the parts of the date with either a hyphen (-) or a slash (/). When defining time formats, you separate each part with a colon (:).
To find out the keystroke numeric code that inserts special characters such as the cent symbol or degree symbol into a custom format, open the Character Map program (Start → All Programs → Accessories → System Tools → Character Map) and then select the characters. Windows displays the Alt+keystroke combination, if one exists, in the lower right of the Character Map dialog box. To use this numeric code, enter the code from the numeric keypad while holding down the Alt key.
To get an idea of how you create your own custom number format, follow these steps for building a format that displays your financial values in the spreadsheet like you do when writing a check, as in 1,234 and 56/100 dollars:
- Enter 1234.56 in a blank cell of the current worksheet to create a sample amount and make sure that this cell is current.
- Choose Format → Cells (Ctrl+1) to open the Format Cells dialog box.
- On the Number tab, select Fraction in the Category list box and then scroll down the Type list and select As Hundredths (30/100).
The Sample area now shows the number in the current cell as 1234 56/100. - Select Custom in the Category list box.
The Type text box now contains the code # ??/00, your starting point for creating your custom format. - Position the Insertion point at the very beginning of the Type list box and then type #,##.
The Type text box now contains the code #,### ??/00, and the sample reads 1,234 56/100. Now, all you have to do is add the stock text and between the whole and fractional digits and the dollars text at the end. - Position the Insertion point after the last pound sign (#) and then type " (open quote), press the
spacebar, type and, and then type " (close quote).
The Type text box now contains the code #,###" and"??/00, and the sample reads 1,234 and 56/100. - Position the Insertion point at the very end of the code (after the last 0) and type " (open
quote), press the spacebar to insert a space, type dollars, and then type " (close quote).
The Type text box now contains the code #,###" and"??/00" dollars", and the sample reads 1,234 and 56/100 dollars. - Click OK to close the Format Cells dialog box and apply the new custom format to the current cell in the worksheet.
When you close the Format Cells dialog box, Excel applies the custom format to the 1234.56 sample number in the current cell. To apply this custom format to other cells in the worksheet, you need to select the cells, open the Number tab of the Format Cells dialog box, select Custom in the Category list box, select the #,###" and"??/00" dollars" code at the very bottom of the Type list box, and click OK.