In addition to the preset options available for formatting numbers in Excel (decimal number, currency, percentage, date, etc.), for specific use cases, additional custom number formats can also be created (coded).
Custom number formats are created and stored in the active workbook and are not available to outside workbooks.
As with other formatting options, (custom) number formats affect only the way a number is displayed and do not affect the underlying value of the number.
Consider the accounting number format:
The format for accounting numbers contains spaces, the € sign, and digit group separators.
The underlying code for the accounting number format can be seen if we switch from Accounting to Custom:
This accounting number format code
_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* “-“?? €_-;_-@_-
can be modified into a new code if needed.
In general, in Excel, each format code consists of four sections separated by semicolons:
format for positive numbers ; format for negative numbers ; format for zeros ; format for text strings
The final specification, format for text strings, is optional.
If sections of the code are skipped, a semicolon must be included for each of the missing sections.
If only one section of the format code is specified, that code is used for all numbers.
If no section is specified, the underlying number in the cell will not be shown at all.
Number formats are coded using the following characters:
digit group separators . or , depending on locale,
decimal point , or . depending on locale,
the number sign # to display the significant digits in a number,
question mark ? to add spaces for non-significant zeros,
character for zero 0 to display non-significant zeros,
at sign @ to point where typed in text is to be displayed (only for format for text strings),
forward slash / for fractions,
percent sign % for percentages
exponent codes used for displaying numbers in scientific notation, E–, E+, e–, or e+
Consider the following example:
Several already available and custom number formats are shown together with their codes.
The character for zero is used to show leading and trailing zeros in the cell B14.
Two digit group separators are used to scale a number by a multiple of 1000000 (format a number to millions) in the cell B16.
Two semicolons are used to make a number invisible (in this way, we’ve coded a number format in which figures are not shown) in the cell B18.
Most text characters must be enclosed in double quotation marks (or preceded with a backslash \ ) in order to be visible alongside numbers.
However, this is not required for the following characters
plus sign +
minus sign –
left parenthesis (
right parenthesis )
colon :
exclamation point !
circumflex accent (caret) ^
ampersand &
apostrophe ‘
tilde ~
left curly bracket {
right curly bracket }
less than sign <
greater than sign >
equal sign =
space character
User currency symbols such as $ or € (as defined at the operating system level) can also be included without quotation marks.
However, currency symbols for other currencies should be encoded in the following way:
[$$-en-US]
[$€-de-DE]
[$BTC-x-xbt1]
For a detailed list, you can check the available Symbols under the Currency number format category.
Consider the following example:
The EUR currency symbol is encoded in cell B2.
The USD currency symbol code is encoded in cell B4.
Negative numbers are indicated by their enclosure within parenthesis in the cell B8.
Negative numbers are indicated by the inclusion of the text “minus” in the cell B10.
Eight colors can be encoded in the format code:
[Black]
[Blue]
[Cyan]
[Green]
[Magenta]
[Red]
[White]
[Yellow]
The color code must be the first item in the code section.
Those can also be combined with conditions (a comparison operator and a value enclosed in subsequent square brackets).
Consider the following example:
Different colors are encoded in cells B2 to B16 for both positive and negative numbers.
Of those, the only one commonly used is the format code used in the cell B19, where only negative numbers are formatted red, and that format is available in the preset options.
For some practical applications of custom number formats, see:
Invisible Numbers with Conditional Formatting
Format Numbers as Thousands, Millions, or Billions in Excel
Dig deeper:
5 thoughts on “Custom Number Formats in Excel”