Sample Custom 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!
(the cells are also formatted negative, zero, and text values as black #N/A #N/A
with a blue font) characters in the General format. 1.666666667 1.666666667
  The text is formatted with a blue font 0.5 0.5
  so that errors do not appear. The other    
  values appear because the number format    
  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
    1003.33 1003 dollars and .33 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