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 |
I have been searching forever online trying to find a format for use with email addresses. It doesn’t need to make it a clickable link. It’s simply to assure people enter a valid email address. Would you mind telling me how to do this and maybe add it to your site for others?
Nathon here you go:
https://simoncpage.co.uk/blog/2009/07/07/excel-vb-validate-email-address/
Thanks so much – I have been looking for a simple explanation of this with examples for so long!
Hi,
I have been trying to frame a custom number format in excel for displaying a number in Hundreds (eg. 12,34,56,789.00). The same is usually displayed in thousands (123,456,789.00).
Pls help me on this.
Rishav
Hi,
I’m trying to format data to a different numer of decimal places demending upon the value. For example within the range 0.001-0.01 to 3dp, from 10.0 to 50.0-1dp and from 100-1000 to no decimal places. Any suggestions you have would be really helpful!
Thanks,
James
@ Rishav
Couple of options for Indian Rupee currency formatting:
1. Custom cell formatting with limitation:
[>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;####0.00
2. VB code as Worksheet_Change event
If however this doesn’t work then why not try this vb module to add in the worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
‘//Written by parry
Dim c
If Target.Cells.Count = 1 Then
Select Case Target.Value
Case Is >= 1000000000
Target.Cells.NumberFormat = “##””,””00″”,””00″”,””00″”,””000.00″
Case Is >= 10000000
Target.Cells.NumberFormat = “##””,””00″”,””00″”,””000.00″
Case Is >= 100000
Target.Cells.NumberFormat = “##””,””00″”,””000.00″
Case Else
Target.Cells.NumberFormat = “##,###.00”
End Select
Else
For Each c In Target
Select Case c.Value
Case Is >= 1000000000
c.NumberFormat = “##””,””00″”,””00″”,””00″”,””000.00″
Case Is >= 10000000
c.NumberFormat = “##””,””00″”,””00″”,””000.00″
Case Is >= 100000
c.NumberFormat = “##””,””00″”,””000.00″
Case Else
c.NumberFormat = “##,###.00”
End Select
Next c
End If
End Sub
@ James
Couple of ways you could use a custom formatting (not ideal though as it is very limited):
[< =0.001]0.000;[<=50]0.0;0 Or preferred route using a VB module like this: Sub FormatDP() Dim cell As Range For Each cell In Selection.Cells If cell.Value <= 0.01 And cell.Value >= 0.001 Then
cell.NumberFormat = “0.000”
End If
If cell.Value < = 50 And cell.Value >= 10 Then
cell.NumberFormat = “0.0”
End If
If cell.Value < = 1000 And cell.Value >= 100 Then
cell.NumberFormat = “0”
End If
Next
End Sub
(simply add this vb code in as a module then you just need to select the cells you want to format and then run the vb module).
HTH – all the best
Hi,
You’ve got a great page here. So far the best I’ve been able to find. Thanks for making this information public. I have an issue that I’ve not been able to find resolution to. I’m trying to develop a custom number format that will display numbers provided in thousands as whole numbers. In other words, the spreadsheet says 1,000.0 and I’d like to display it as 1,000,000.
I’m trying to do it through custom formating because I have a different application that I’m going to use it in that actually recognizes excel custom formats.
I’d appreciate any wisdom you might have.
This one is challenging.
I want a cell to be formatted as % if value 100. I tried using custom format:
[>100] $#,###.00;[100 and 10000, it displays the $ sign but the value shown is 100 fold too small. If I type in 1234567, I get $12,345.67.
If this can’t be done with custom formats, is there a w ay to do with Conditional Formatting? I couldn’t get Conditional Formatting to do any formatting other than color changes.
I’m reposting. The dialogue box clipped some of long sentences,
so I’m re-posting with
to break up longer sentences.
Sorry for inconvenience.
============================
This one is challenging.
I want a cell to be formatted as % if value 100.
I tried using custom format:
[>100] $#,###.00;[100 and 10000, it displays the $ sign
but the value shown is 100 fold too small.
If I type in 1234567, I get $12,345.67.
If this can’t be done with custom formats,
is there a way to do with Conditional Formatting?
I couldn’t get Conditional Formatting to do
any formatting other than color changes.
I would like the cell to be formated so that when I enter a number for exemple 8 it shows 1
I am basically trying to insert the number of hours worked and displaying it in days. Assuming a days work is 8 hours.
I want to to be in cell formating so that I do not have to indert a formula each time I fill the cell
thanks
Can a customnumber format condition based on error codes?
for example if the cell’s value is #N/A make the number format “”?
or if the result is #DIV/0! make it show “Cannot Divide by 0″… etc
Thanks in advance.
A fascinating discussion is definitely worth comment. I do think that you ought to publish more on this issue,
it may not be a taboo subject but generally people don’t talk about such subjects. To the next! All the best!!
Hi,
Can there be a nested if elseif function? I have this, but it doesn’t work when the range extends to negative numbers.
[>=1000000]#,##0.00,,”m”;[>=10000]#,##0.00,”k”;#,##0
Many Thanks!
Hi,
I am trying to format a cell so that when I input a number it shows up with the ° symbol. I have created a spreadsheet that deals in degrees and I would like all of the numbers in this format.
Thanks for your help.
Jeff
Do you know of a way to create a formulaic hyperlink?
A cell formulat that creates a clickable hyperlink to a dynamic location. For example, http://www.amazon.com/dp/B0039L2XG6 , where Amazon’s part number (B0039….) is joined into the hyperlink as a part of a formula.
Hence, you could have a column of cells that displayed clickable links against a list of part numbers.
Awesome page, by the way. Thank you.
Looking for a cell format code that will allow you to display your text in a cell but not print out.
Hi, great page and very useful. I am in England and I run a building site…No-one on this site seems capable of typing in correct english so the day sheets end up looking like “gobboldy gook”
I am trying to create a “Number Code” that converts whatever is entered into “Title”, “lower”,”CAPITALS” or “Proper” text as it is typed. Is this possible.
I think the best way would be to create a VBA function – if you send me an example sheet I’ll show you.
Hiya,
I’m trying to ensure that telephone numbers (in the UK) are shown and entered in the following format:
0115 9123452 or 01623 123456
As you can see some area codes have 4 digits others have five, and the following part of the telephone code can vary from five to seven.
Any help on this would be great as they must be entered with an area code and with a space in between the area code and the rest of the number.
Also I need a similar format for post codes, again some have three first then another three, some have four first followed by three. They again must have a space between.
Thanks
I am trying to have a formatting where the entire number is always 4 digits, so for example 1.365 or 23.25 or 445.3 or 5678 without digits for anything greater than 9999.
I am able fairly easily to make it work for number under 100 with the following formula :
[<10]_(0.000_);[<100]_(0.00_);_(0_)
but for numbers above 100 excel is giving an error with the following formula
[<10]_(0.000_);[<100]_(0.00_);[<1000]_(0.0_);_(0_)
any ideas ?
I am trying to add space between letters and numbers. ex. “WYW181703” to “WYW 181703” with exactly four spaces between the letters and numbers. I don’t want to use a formula but need to format this. Thanks for any help you can provide!
I am trying to create a format for our 19 digit account number and for some reason it always turns the last for numbers into zeros.
I need a format of ###-##-####-##-###-##-###-\A to produce an account number like this one: 211-61-6399-82-849-24-135-A and it keeps turning up like this: 211-61-6399-82-849-20-000-A
Why is it doing this???
Hi
Is it possible to enter a 4 digit number into say cell A1 eg. 0432 & somehow format this so it changes cell A1 to a time format of 04:32
Simon, Sorry for the delay in answering…I got sidetracked a bit there. I want the desired effect on any Excel spreadsheet so just code it up on a blank sheet. I can send you a blank copy of the Day Sheet if you tell me how I do that? Steve
i am trying to calculate the data in another cell by Custom formatting..Example: in Cell A1 i type 25+45 in cell B1 i want the answer as 70 by giving jst reference of A1 and using Custom format… So please provide me Custom format code for this
Hi,
I have been trying to frame a custom number format in excel for displaying a number in Ten thousand. How to ruduce 10,000,000 display as 1,234.00 ?
Please help me on this. Thank you.
I have a cells set up as 00.0° to display the degree symbol after I input a number but when I add text after the number the degree symbol will not display.
I am trying to create a retail price that will be displayed on a sign in a store. So if the price is $7.99, for example, I would like the “$” displayed in superscript, the “7” displayed normally, and the “99” in superscript without any decimal displayed. I have looked for fonts that might do this, but could not find. I am hoping Excel has a way to get this done. Thanks very much!
Am trying to conditionally format cells that are already custom formatted for thousands (k). Need to keep the custom formatting. We had old formulae in outlined box with light purple to flag it but we no longer need to use those formulas. I use an if statement predicated on the date chosen in the drop down box of the executive report which is linked to $E$2. The area we had highlighted and outlined was $H24:$L32. The formula is =IF(($E$2<37), SUM(NPSupplyPercentage*TotalGolfCourseUsage), "") which works very nicely. If we go back to December of 2013 the formulas are there and work, starting in January 2014, the formulas disappear. I would (greedily) like to keep the nice lavender fill with black outline for the area where the formulas exist (prior to January 2014) but "conditionally" want the fill and outline to disappear as do the formulas when we get to this year. Any suggestions? VBA? Can a cell be formatted with custom (100,064,000 = 100,064k) and conditionally formatted? Thank you in advance.
Hi,
Is there a limit to the amount of conditions you can apply to custom formatted Cells?
ie.
[Red][=2]”▼”;[Green][=0]”▲”;” ”
Seems to crash if
[Red][=2]”▼”;[Green][=0]”▲”;[Blue][=3]”■”;” “
Hi,
i want to format the cells as Standard when they are integer, and 0,?? when decimal, and i’d like the standard values to be aligned with the decimal ones.
Example 7 and 7,25 should be shown exactly like that.
In other words, I don’t want to see 7,00 but 7 without a comma.
Any idea ? Thank you.
Hi, just to precise my question above:
I have both formats ???,?? and ???_,_0_0
this works fine but how can I combine them within 1 formula ?
Ideally something like [mod(1)=0] but this is not accepted…