In the past Chandoo has written about custom Number Formats for cells:
http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/
http://chandoo.org/wp/tag/custom-cell-formatting/
and I have written about Custom Number Formats for Charts:
http://chandoo.org/wp/2011/08/19/selective-chart-axis-formating/
http://chandoo.org/wp/2011/08/22/custom-chart-axis-formating-part-2/
This post examines a technique for quickly developing Custom Number Formats for Cells, Charts or any other Number location in Excel.
A Technique for Quickly Developing Custom Number Formats
Instead of Selecting the cell, chart axis etc, Ctrl 1, Format Cells/Properties, Number Tab, Custom and then entering a Custom Format and Apply, only to find out that the format is incorrect, try this simple technique below.
1. Enter a few Numbers in 3 cells
Enter 3 numbers, a positive, zero and negative which have values you will expect to receive in your model.
2. Add a Custom Format Cell
In D3 I have entered ##,;-(##,);”Zero”
3. Display Numbers using the custom Format
Each Number to a display cell with a simple =Text(B3,$D$3)
Copy down
This will display the 3 numbers using the Custom Format in Cell D3
4. Develop Your Custom Format
Play around with your own Custom Number Formats to your hearts content
5. Use your new format
Once you have completed your new Custom Number Format, copy the cell contents of D3 in this case.
Select your cells/or other Excel Numbers,
Ctrl 1,
Format Cells/Properties,
Number Tab, Custom
Enter the Custom Format and Apply.
6. Extending the Technique
This technique can be extended by adding several more rows with a larger range of values.
The values are all evaluated at the same time
LIMITATIONS
The above technique does not show the effects of the Color Modifiers in the test cells
But I think it is a safe bet that you will understand what the Modifier [Red] will do
There are also reserved characters such as E
So in the above example if I had used Zero instead of “Zero”
It would have displayed Ze1900ro, where the E in Zero is taken as 10^x and x=0 so Excel interprets e as 0 or 1900, a date?
You can avoid this by using the code “Zero” or Z\ero
DOWNLOAD
You can download the worked Example File used above.
NUMBER FORMATS
For more on Number Formats check out the above links or those below:
http://www.ozgrid.com/Excel/excel-custom-number-formats.htm
http://www.ozgrid.com/Excel/CustomFormats.htm
http://peltiertech.com/Excel/NumberFormats.html
27 Responses to “A Technique to Quickly Develop Custom Number Formats”
Thanks Hui, an excellent tip.
Saves me from using a choose formula to change format types in a dashboard which returns metrics with different number types (%, $s, units). I can build in a lookup for the Text type instead 🙂
Brilliant Hui! To go further, do you a site/listing of all the "special" characters used in number formats? E.g: E,$,%,#...?
I'd like to add that the same format strings that you develop in column D can be used in the VBA Format() function.
Wow! This is so cool! Thanks, Hui!
Thanks, Hui! This is a really cool trick 🙂
Brilliant approach, thank you Hui. I have always found custom formatting fiddly & frustrating - but this approach conquers them.
John
very helpful article, thank you very much.
I still seeking a nonVBA method to address a common need.
Display currencty number as words...for example '22.05' format to display this value as 'twenty two dollars and five cents'
Thanks again for a super article.
Hui
Do you reckon you could explain this bit of the custom formatting a little more ie the positive part of the settings, I just don't get it, how did you tell it to scale to millions?
"$M" #,,.#;
thanks
John
@John
Lets look at the whole text
“$M” #,,.#;
.
The “$M” part is purely a text addition at the front of the number
.
The #,, part
The # says put the number here
Each , says divide the number by 1000
So the #,, says put the number here and divide it by 1,000,000
.
the last part .# says put one number after the decimal
.
So a number 7,654,321.98 will be displayed as $M 7.7
using the code above.
.
Remember that this is only how the number is displayed
The actual cell still contains the number 7,654,321.98
@Salmon
Have a read of http://chandoo.org/forums/topic/how-to-convert-numbers-to-text
Hui I really appreciate you taking the time with this article and explanation. I never knew any of that and my eyes used to glaze over when it came to custom formatting until now.
@John
No Probs
Just remember that this is only 1 part of the 4 parts of the Custom Number formats
The overall format is:
Positive Format ; Negative Format ; Zero ; Text
and so each section above can be formatted separately.
Hi Chandoo
AFAIK
you can't use color in Text Function
=TEXT(B3,"""$M"" #,,.#;[Red]""Unacceptable"";""Zero""")
Excel will not accept the color
Sorry if I'm missing something (or just being pedantic) but shouldn't "7,654,321.98" round to $M 7.7?
Love the handy tips!!
@Adam
Yes
“7,654,321.98? does round to $M 7.7 with 1 decimal place
“7,654,321.98? does round to $M 7.65 with 2 decimal place
is there anywhere it doesn't?
@Hui
The only time the rounding is wrong is in your response to @John, which unfortunately for me was the only one I noticed!!
I shall take my pedantry elsewhere!!!
@Adam
Thanx for that pickup
I have edited my response to John
@Salmon:
http://www.linkedin.com/groupItem?view=&gid=3843467&type=member&item=78700058&qid=e8bfc676-b12d-49f3-839e-8c11b747c516&trk=group_most_popular-0-b-ttl&goback=%2Egmp_3843467
The links to the above given sites do not work. They give an Internal Server Error. Please arrange to rectify them.
http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/
http://chandoo.org/wp/tag/custom-cell-formatting/
@Murali
Thanx, All Fixed
I'll have to kick the proof reader in the ....
Hui,
Great idea.
I would dito Luke M. On the page within the model used to explain the model style (ie yellow fill for WIP, blue text for input cells, black for formulae etc) you should add in the special formats used and then link to them in the TEXT() formula.
Hi chandoo.
i want to know that if our data is not maching but i wana mension on ather sheet so how can do it. please give me a nice idea,
Thanks
Rajeev Gupta
[...] http://chandoo.org/wp/2011/11/02/a-technique-to-quickly-develop-custom-number-formats/ [...]
I want to know, if I enter a number in to one cell it want to show Multiple By 5. how can we make custom format?
Eg: Im entering 10. But that cell want to display 50.
[…] A Technique to Quickly Develop Custom Number Formats from Chandoo […]
I want only decimal value. if i have the value 5.35, for calc purpose i need only .35 not 5, how can i get this?
Please help
@Datta
There is no way using Custom Number Formats to do what you want
You can get similar results by using formula
I assume B2 has the value 5.35
As a number
=B2-INT(B2)
Result: 0.35
As text
=RIGHT(B2,LEN(B2)-FIND(".",B2)+1)
Result: .35