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 NumbersStrings (Text) and LogicalsErrors 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 StringNumbersStrings
(Text) /
Logicals
Errors
Positive
(>0)
Negative
(<0)
Zero
41234default
3123defaultdefault
2121defaultdefault
1111defaultdefault

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.

CodeFormatsFunction in a Formatting String
GeneralNumber, Date, Time, TextThe 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, TextSeparates the four sub-zones of the custom format string.
#NumberDigit 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.
0NumberDigit 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.
?NumberDigit 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)NumberDecimal 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)NumberThis 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.
%NumberPercentage symbol. Shifts the decimal point two places to the right and appends the % character for display.
E+NumberExponential (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-NumberExponential (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)NumberExcept 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)NumberExcept as discussed in the E+ format, displays a plus symbol. Negative numbers show erroneously as positive when included in default zone two.
$NumberDisplays the $ currency symbol.
( )NumberDisplays the characters. They are commonly used in Accounting and Currency formats to adjust formatting for negatives.
/ (slash)Number, DateSeparates the months, days and years in date formats. Also separates the numerator and the denominator in fractional formats.
dDateDisplays the day of the month without leading zeroes (1-31).
ddDateDisplays the day of the month with leading zeroes for days before the tenth day (01-31).
dddDateDisplays the day of the week as an abbreviation (Sun-Sat).
ddddDateDisplays day of the week in full (Sunday-Saturday).
hTimeDisplays the hours without leading zeroes (1-23).
hhTimeDisplays the hours as a number with leading zeroes (01-23).
[h]TimeDisplays the hours without leading zeroes, includes the integer portion of the value as part of the time to be displayed.
[hh]TimeDisplays the hours with leading zeroes, includes the integer portion of the value as part of the time to be displayed.
mDate, TimeIn 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.
mmDate, TimeIn 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.
mmmDateDisplays the name of the month as an abbreviation (Jan-Dec).
mmmmDateDisplays the name of the month in full (January-December).
mmmmmDateDisplays the capital letter of the month name.
sTimeDisplays the seconds as a number without leading zeroes (1-59).
ssTimeDisplays the seconds as a number with leading zeroes (01-59).
ss.00TimeDisplays the seconds as a number with leading zeroes and with hundredths of a second (00.00-59.99).
yyDateDisplays the year as a two-digit year (00-99).
yyyyDateDisplays the year as a four-digit year (1900-9999).
: (colon)TimeSeparates the hours, minutes and seconds in time formats.
A/PTimeSets 12 hour time format, displays AM for =12:00.
AM/PMTimeSets 12 hour time format, displays AM for =12:00.
\ (backslash)Number, Date, Time, TextDisplays 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, TextAny 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, TextThe space character can be used for spacing in any format string.
* (asterisk)Number, Date, Time, TextThis 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, TextPads 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.
@TextDisplay 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, TextChanges 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, TextMay 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]NumberSpecifies 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.