fbpx
Search
Close this search box.

A Technique to Quickly Develop Custom Number Formats

Share

Facebook
Twitter
LinkedIn

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

 

 

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

27 Responses to “A Technique to Quickly Develop Custom Number Formats”

  1. Geoff says:

    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 🙂

  2. Luke M says:

    Brilliant Hui! To go further, do you a site/listing of all the "special" characters used in number formats? E.g: E,$,%,#...?

  3. I'd like to add that the same format strings that you develop in column D can be used in the VBA Format() function.

  4. Fred says:

    Wow! This is so cool! Thanks, Hui!

  5. Michael Azer says:

    Thanks, Hui! This is a really cool trick 🙂

  6. John@ReddyBay says:

    Brilliant approach, thank you Hui. I have always found custom formatting fiddly & frustrating - but this approach conquers them.
    John

  7. Salmon says:

    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.

  8. John@ReddyBay says:

    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

  9. Hui... says:

    @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

  10. john@reddybay says:

    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.

  11. Hui... says:

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

  12. Yahya Hussien says:

    Hi Chandoo
    AFAIK
    you can't use color in Text Function
    =TEXT(B3,"""$M"" #,,.#;[Red]""Unacceptable"";""Zero""")

    Excel will not accept the color

  13. Adam says:

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

  14. Hui... says:

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

  15. Adam says:

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

  16. Hui... says:

    @Adam

    Thanx for that pickup
    I have edited my response to John

  17. Murali says:

    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/

  18. Hui... says:

    @Murali
    Thanx, All Fixed
    I'll have to kick the proof reader in the ....

  19. Clarity says:

    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.

  20. Rajeev Gupta says:

    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

  21. mohmed says:

    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.

  22. […] A Technique to Quickly Develop Custom Number Formats from Chandoo […]

  23. Datta says:

    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

    • Hui... says:

      @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

Leave a Reply