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 |