Custom number format strings are strings of one or more characters that symbolize how a value is to be displayed. They are used in cell number formatting and may (with some exceptions) be used by the TEXT function to produce formatted text strings.
Custom formatting only affects the display of Numbers, Text Strings and Logicals. Errors always appear using the default display rules in Excel. When Logicals are formatted, they follow the rules for text strings and are just displayed as text, there is no value-driven alternative formatting for the two possible values.
There may be up to four zones or sub-parts of a custom format string, each separated by a semi-colon. The values affected by which sub-part of the string is shown in the table below.
| Zones in Format String | Numbers | Text Strings/ Logicals |
Errors | ||
| Positive (>0) |
Negative (<0) |
Zero | |||
| 4 | 1 | 2 | 3 | 4 | default |
| 3 | 1 | 2 | 3 | default | default |
| 2 | 1 | 2 | 1 | default | default |
| 1 | 1 | 1 | 1 | default | default |
The first three zones of the format string may be re-purposed to determine the ranges of values for which the three zones apply, as discussed under “condition value” in the table below.
Many display formats can be set using standard settings such as Percentage, Number and Currency in the Format Cells dialog, which all translate internally into custom formatting strings. By first selecting the format on the Number tab of the Format Cells dialog box and then clicking on the Custom option, the strings underlying the standard formats can be displayed.
One way to build a special formatting string is to first select a built-in format, setting the number of decimals, currency symbol, thousands separator etc. and then click the Custom option and edit in the extra formatting characters required in the Type box.
The table below sets out the code characters and summarises their usage in custom formatting strings. Note that those identified as being able to be used to format strings also apply to the formatting of Logical values.
| Code | Formats | Function in a Formatting String |
| General | Number, Date, Time, Text | The default format that shows the maximum amount of information in the available space. For numbers to large to fit in the cell, uses Exponent format. |
| ; (semi-colon) | Number, Date, Time, Text | Separates the four sub-zones of the custom format string. |
| # | Number | Digit placeholder for a number. If there is no significant digit to occupy the position held by a #, no space or character is output in the displayed value. |
| 0 | Number | Digit placeholder for a number. If there is no significant digit to occupy a position held by a 0 in a formatting string, that digit position is zero-filled. |
| ? | Number | Digit placeholder for a number. If there is no significant digit to occupy the position held by a ? in a formatting string, its position is padded with the space required by a zero character. This character is also commonly used to build fractional formats. |
| . (period) | Number | Decimal point. If included, indicates that digit placeholders to its right represent decimal places. Not required. May also be used to create time formats that display decimals of a second. Shown here as a period as that is most common on Australian systems, but the system's regional settings determines the actual character used. |
| , (comma) | Number | This character has two tasks. Number-grouping symbol (sometimes called the thousands separator). Also: Scale character. For each number-grouping symbol that follows the final digit placeholder in a formatting string, Excel divides the displayed result by 1000. Displayed here as a comma as that is most common on Australian systems, but the system's regional settings determines the actual character used. |
| % | Number | Percentage symbol. Shifts the decimal point two places to the right and appends the % character for display. |
| E+ | Number | Exponential (scientific) format. If a format contains a 0, # or ? to the right of an E+ the number is displayed in exponent format, an E followed by the exponent is displayed, including a sign symbol. |
| E- | Number | Exponential (scientific) format. If a format contains a 0, # or ? to the right of an E+ the number is displayed in exponent format, an E followed by the exponent is displayed, including a sign symbol only for exponents with negative sign (+ not displayed). |
| - (minus) | Number | Except as discussed in the E- format, displays a minus symbol. Positive numbers show erroneously as negative when included in default zones one or three. |
| + (plus) | Number | Except as discussed in the E+ format, displays a plus symbol. Negative numbers show erroneously as positive when included in default zone two. |
| $ | Number | Displays the $ currency symbol. |
| ( ) | Number | Displays the characters. They are commonly used in Accounting and Currency formats to adjust formatting for negatives. |
| / (slash) | Number, Date | Separates the months, days and years in date formats. Also separates the numerator and the denominator in fractional formats. |
| d | Date | Displays the day of the month without leading zeroes (1-31). |
| dd | Date | Displays the day of the month with leading zeroes for days before the tenth day (01-31). |
| ddd | Date | Displays the day of the week as an abbreviation (Sun-Sat). |
| dddd | Date | Displays day of the week in full (Sunday-Saturday). |
| h | Time | Displays the hours without leading zeroes (1-23). |
| hh | Time | Displays the hours as a number with leading zeroes (01-23). |
| [h] | Time | Displays the hours without leading zeroes, includes the integer portion of the value as part of the time to be displayed. |
| [hh] | Time | Displays the hours with leading zeroes, includes the integer portion of the value as part of the time to be displayed. |
| m | Date, Time | In a date format, displays the month of the year as a number without leading zeroes (1-12). In a time format, displays the minute of the hour as a number without leading zeroes (1-59). The presence of a colon (:) indicates a time format. |
| mm | Date, Time | In a date format, displays the month of the year as a number with leading zeroes (01-12). In a time format, displays the minute of the hour as a number with leading zeroes (01-59). The presence of a colon (:) indicates a time format. |
| mmm | Date | Displays the name of the month as an abbreviation (Jan-Dec). |
| mmmm | Date | Displays the name of the month in full (January-December). |
| mmmmm | Date | Displays the capital letter of the month name. |
| s | Time | Displays the seconds as a number without leading zeroes (1-59). |
| ss | Time | Displays the seconds as a number with leading zeroes (01-59). |
| ss.00 | Time | Displays the seconds as a number with leading zeroes and with hundredths of a second (00.00-59.99). |
| yy | Date | Displays the year as a two-digit year (00-99). |
| yyyy | Date | Displays the year as a four-digit year (1900-9999). |
| : (colon) | Time | Separates the hours, minutes and seconds in time formats. |
| A/P | Time | Sets 12 hour time format, displays AM for <12:00 and PM for >=12:00. |
| AM/PM | Time | Sets 12 hour time format, displays AM for <12:00 and PM for >=12:00. |
| \ (backslash) | Number, Date, Time, Text | Displays the next character in the formatting string as a literal - same as if that character was in quote marks. The backslash is not displayed in the output. |
| "string" | Number, Date, Time, Text | Any string of one or more characters can be added to the output result by enclosing the string in quotes at the appropriate point in the formatting string. More than one string may be used in the format. |
| (space) | Number, Date, Time, Text | The space character can be used for spacing in any format string. |
| * (asterisk) | Number, Date, Time, Text | This character causes the displayed result to fill the cell. The content formatted by the code to the left of the asterisk is pushed to the left-hand edge of the cell and that formatted by the code after the asterisk is pushed to the right-hand side. The character following the asterisk is used to pad the space between. Ignored in TEXT function number format strings. The asterisk and the following character are both ignored. |
| _ (underscore) | Number, Date, Time, Text | Pads the output with the space the next character in the format string requires in the given font face at the given size. Used to manage numeric column alignment. |
| @ | Text | Display the contents of the cell as a string. If the sole character in the format string, anything entered in the cell is stored and displayed as a string. Commonly used in zone 4 for string formatting. |
| [BLACK] [BLUE] [CYAN] [GREEN] [MAGENTA] [RED] [WHITE] [YELLOW] [COLORnn] | Number, Date, Time, Text | Changes the foreground (font) colour for the displayed text. Only one colour may be used per format zone, however different colours can be used in different zones. The named colours have defined RGB output colours. The numbered colours depend upon the colour settings in a workbook and the displayed results will change when those colours are modified. Use of red code for negatives is common. If the standard colours are in use in a workbook, [COLOR10] is generally a better choice of colour for green than [GREEN]. |
| [=value] [<>value] [ [>value] [>=value] | Number, Date, Time, Text | May be used in zones 1 and 2 of a custom number format string to provide formats which apply to numbers based on their comparison to key cut-off values. Replace "value" in these codes with the actual number to which the cell's value should be compared, and use the appropriate comparison operator as shown, such as [>=1000000]. |
| [$iso] | Number | Specifies the display of an ISO currency code, such as "AUD" or "USD". Replace "iso" in this code with the three-letter ISO currency code, such as [$AUD] or [$USD]. |
The table above can be sorted by formatting code and filtered by data type the format code can be used for formatting.
