# 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 |

Tweets by @simoncpage

## About

You’re currently reading “Excel Custom and Conditional Number Formatting”.

- Published:
- Tuesday, September 16th, 2008

- Author:
- Simon Page

- Category:
- Excel

## Related

## Categories

- Artwork (75)
- Design Inspiration (33)
- Design Resources (10)
- Excel (12)
- Filemaker (5)
- Gadgets (26)
- Music (3)
- Photography (3)
- Photoshop (1)
- Press (7)
- Random (5)
- Software (4)
- Video Gaming (15)
- Wallpaper (6)