Excel Custom and Conditional Number Formatting

Finding Excel’s custom number formatting confusing? Below is a good reference for some of the more popular examples of formatting codes for numbers and text data in Excel.

Sample Custom & Conditional Number Formats

Format Code Description Data with General Format Data with Custom Number Format
00000 Always displays 5 digits. Pads with leading 983 00983
zeros if the number contains fewer than 5 digits. 23589 23589
This custom format is very useful when you 9856 09856
work with zip codes. 85632 85632
0;-0;;@ Suppresses zeros in cells. 98 98
This format displays positive values (0) and 0
negative values (-0), hides zero values, and -9 -9
displays text (@). 0
hello hello
;;;@ Suppresses numbers in cells. 98
This format hides positive, negative, and zero 0
values, and displays only text (@). -9
0
hello hello
[Black]General Suppresses errors in cells. 0.333333333 0.333333333
This format hides or displays positive, #DIV/0! #DIV/0!
negative, zero, and text values as black #N/A #N/A
characters in the General format. The cells 1.666666667 1.666666667
The other values appear because the number format 0.5 0.5
color overrides the font color for the cell.
#.??? Lines numbers up with the decimal. 3.256 3.256
The ? code leaves a space for 5.2 5.2
insignificant zeros but does not 9.652 9.652
display them. 98.2568 98.257
#, Displays numbers in thousands. 4058.34 4
The comma is the thousands 52865 53
placeholder. If you wish to display 236
the numbers in millions, use the 5502235623 5502236
format #,, instead. 999555 1000
#,###,, “M” Displays numbers in millions. 32654236 33 M
The comma is the thousands 4563258963 4,563 M
placeholder. The letter “M” is displayed 1.2357E+12 1,235,699 M
after each number. 22333666 22 M
12345678 12 M
0.00,, Represents numbers in millions. 1000000 1.00
This number format displays numbers 12000000 12.00
so that 1 represents one million. 12200000 12.20
120000 0.12
0;[Red]”Error!”;0;[Red]”Error!” Displays “Error!” in red for negative numbers 10 10
and text. hello Error!
This format may be useful to alert users -10 Error!
that they entered invalid text in a cell. 0 0
0.0° Displays numbers with the degree symbol. 32.63 32.6°
The degree symbol uses the character 63.258 63.3°
map code ALT+0176. 96.75 96.8°
-5.36 -5.4°
0.00 £ Displays numbers with the British pounds 100 100.00 £
symbol. 67.63 67.63 £
The pound symbol uses the character 0 0.00 £
map code ALT+0163. 9.63 9.63 £
0.0_-;0.0- Displays the negative sign on the right side -5 5.0-
of the number. -40.3 40.3-
This format also pads space at the right 50 50.0
of a postive number so that the decimals line up. -10.99 11.0-
@ Displays 5 spaces and then the text to this this
give the appearance of a tab (or indent). is is
(5 spaces, then @) a a
test test
@*- Shows text leaders. Apples Apples ———————
In a number format, the asterisk (*) Oranges Oranges ——————-
causes Microsoft Excel to repeat the next Bananas Bananas ——————-
character until the width of the column Pears Pears ———————-
is filled. Text leaders are commonly used in Peaches Peaches ——————-
tables of contents. Plums Plums ———————-
Grapes Grapes ——————–
0 “dollars and” .00 “cents” Displays a currency value with words. 20.36 20 dollars and .36 cents
This format displays the whole number 2.55 2 dollars and .55 cents
portion of the number followed by the 45.36 45 dollars and .36 cents
words “dollars and,” followed by the fractional 69 69 dollars and .00 cents
portion of the number and the word “cents.” 36.25 36 dollars and .25 cents
0″.”00 Displays a value in hundreds. 2300 23.00
The comma is used only for scaling 23 0.23
numbers in multiples of one thousand. Use 400 4.00
a number format with a decimal character 5000 50.00
between the placeholders to display a -90 -0.90
number as a multiple of 100 or 10 (0″.”0). 0 0.00
[>9999999](000)000-0000;000-0000 Displays a telephone number with or without an 7045556325 (704)555-6325
area code. 9106325689 (910)632-5689
If the number is greater than 9,999,999, this code 8896523 889-6523
displays the number with an area code 5362563 536-2563
((000)000-0000); otherwise the number appears 2065896325 (206)589-6325
without the area code (000-0000). 3369856 336-9856
[<1].00¢;$0.00_¢ Shows currency values in dollars or cents. 1.25 $1.25
This code displays values less than 1 in 3 $3.00
cents notation (.00¢), and display values 0.35 .35¢
greater than or equal to 1 in dollars, and 0.95 .95¢
leaves a space on the right so that the 22.36 $22.36
decimals line up ($0.00_¢). 0.75 .75¢
[<=2]”Low”* 0;[>=4]”High”* 0;”Average”* 0 Using “If, ElseIf, Else” in a number format: 1 Low                               1
If the value is <=2, display the word “low” with the value, 2 Low                               2
Else If the value is >=4, display the word “high” with the value, 3 Average                        3
Else display the word “Average” with value. 4 High                              4
5 High                              5
[Red][<=2]0;[Green][>=4]0;[Black]0 Using “If, ElseIf, Else” in a number format: 1 1
If the value is <=2, display the value with red text, 2 2
Else If the value is >=4, display the value with green text, 3 3
Else display the value with black text. 4 4
5 5