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

33 thoughts on “Excel Custom and Conditional Number Formatting”

1. Nathon Dalton

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?

2. Rishav Sethia

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

3. James

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

4. Simon Page Post author

@ 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

5. Simon Page Post author

@ 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

6. Jim

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.

7. LarryFromVegas

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.

8. LarryFromVegas

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.

9. anne

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

10. Scott Gall

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

11. toan

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

12. Mary Jo

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!

13. Jeff

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.
Jeff

14. Ed

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.

15. Jerry Coon

Looking for a cell format code that will allow you to display your text in a cell but not print out.

16. Stephen Smith

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.

17. Lucy Scattergood

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

18. youssef

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 ?

19. Nikol Noll

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!

20. Rreyes

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

21. Matthew

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

22. Stephen Smith

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

23. Hitesh

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

24. Zoey

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 ?

25. don

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.

26. Yiannis Tsouchlarakis

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!

27. Ginny

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.

28. BenPEllen@gmail.com

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]”■”;” “

29. jcs@Ffm

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.

30. jcs@Ffm

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…