Selective Chart Axis Formating

Posted on August 19th, 2011 in Charts and Graphs , Excel Howtos , Huis , Learn Excel , Posts by Hui - 51 comments

John wanted a chart with only the 80 highlighted as £80 and the rest of the axis as normal 10, 20 etc

This involved a simple Custom Number Format being applied to the Y Axis, Number Format of the Chart.

Custom Format: [=80]”£ “0;0;0;

IS THAT ALL

That Simple Custom Number format tells excel to apply at custom format of “£ “0 when the value is 80, otherwise use a normal 0 custom format.

“£ “0 means, Display a £ and a space in front of the number with no decimals

0 means, Display the number with no decimals

 

WHAT ELSE CAN I DO WITH CUSTOM NUMBER FORMATS AND CHARTS

This technique can be extended in a number of areas

Highlight Just a Single score

Custom Format: [=80]”£”0;;;

 

Highlight Two Scores

Custom Format: [Blue][=80]”%”0;[Red][=100]”P”0;

Using Wing Ding, Web Dings and Other Characters

Custom Format: [Blue][=100]”4″;[Red][=80]”8″;

and the Web Dings Font

Custom Format: [Blue][=70]”P”;[Red][=90]”O”;

and the Wing Dings 2 Font

 

SO HOW DO THESE WORK?

As with cells you can apply custom Number formats to any of the Charts numerical objects including the Charts Axis and Data Point Labels.

1. Select the Chart and then the Axis or Data Point Labels.

2. Right Click and select Format Axis

3. Select the Number Tab

4. Custom Format

Put the custom format in the Format Code dialog and Add

5. Close the Format Axis Dialog


CAUTION

If you setup a fancy Custom number Format as say

[Blue][=100]”4″;[Red][=80]”8″;

and then set the axis scaling as Minimum 10, Major Unit 20, the two number 80 and 100, from the Custom format will not display as Excel will skip them according to the Axis Scale.

 

Downloads

You can see how all the above Charts are made using the Sample Data File Attached

Version – Excel 1997/03, Excel 2007/10.

 

LINKS

The links below will explain the intricacies of Custom Number Formats.

http://www.ozgrid.com/Excel/excel-custom-number-formats.htm

http://www.ozgrid.com/Excel/CustomFormats.htm

http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/

The formats described above can be used as equally well with Charts Numbers as with Cell Numbers.

 

WHAT ARE YOUR FAVORITE CUSTOM NUMBER FORMATS ?

What are your favorite custom number formats?

Share your custom formats or ideas in the Comments below:

 

Hui…

 

For a list of my other contributions at Chandoo.org please visit: Hui…

 

 

Written by Hui...
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

51 Responses to “Selective Chart Axis Formating”

  1. Rohit1409 says:

    This is really new an unexplored by me.. i guess 8yrs+ working on excel is still nothing 😉

    Cheers
    Rohit1409

  2. Jon Peltier says:

    Went a bit crazy with the formatting on those bar charts, eh?

    I wrote a detailed article on number formats in Excel: http://peltiertech.com/Excel/NumberFormats.html

  3. Eric says:

    Wow, talk about timely help... I've been struggling with this all week. Thanks!

  4. Luke M says:

    Nice post Hui. I'd known about using custom formats in cells, but I never thought to use them in charts. =)

  5. Tom Quist says:

    Cool! At first, I thought "why would anybody want to do this, but your examples got me thinking about some possible applications. I will try this on my own, but could you do something like this: [Blue][<100]; ? Would that result in the axis labels with values below 100 being blue, and above 100 being red?

  6. Tom Quist says:

    Just visited Jon's link and got the answer to my question! Thanks, Jon. You can be a bit bristly, but you do have some good knowledge and information to share.

  7. Tom Quist says:

    @Jon - that is truly an excellent post. It answered a couple of questions I've had for some time. One format that I didn't find on your post was how to do the following:

    12,345,100 = 12,345K

    Is this possible? I did see how to do what I believe would have resulted in 12345.10K.

    Thanks,
    Tom

  8. Hui... says:

    12,345,100 = 12,345K
    Try 0,000,K

  9. Jon Peltier says:

    Bristly? Yeah, my wife wishes I'd shave more regularly...

  10. Tom Quist says:

    @Hui...

    Thanks for the reply. Yes, this does work, however, I'm using the formatting on a chart axis and some of the numbers may not be above 1M. Those numbers end up looking like this:

    30,000 = 0,030K

    I could tell it to apply the format only if it's over 1M, but I'm curious if there is an easier way.

    Thanks!

    Tom

  11. Tom Quist says:

    @Jon - I just chortled. Sorry if I offended - I could have left the bristly part out. That's what she said - oh, snap!

  12. Hui... says:

    @Tom
    [>1000000]0,000,K;[>1000]0,000;0

  13. Jon Peltier says:

    Tom -
     
    Try "#,##0,K" (the # means only if that place is needed).
     
    And I know what you mean about "bristly". My keen sense of irony doesn't always come across in plain text.

  14. Tom Quist says:

    @Hui & Jon - thanks to both of you.

    Jon - Weird. Your proposed formatting works great within a cell, but, oddly, it seems that your solution only works on a chart label when I leave off the second comma.

  15. Tom Quist says:

    @Jon - BTW, I understand the plain text conundrum. That's why Al Gore included emoticons when he invented the internet 🙂

  16. mustafa says:

    like it too much, thanks..

  17. Fred says:

    This is very cool indeed. Thanks Hui for teaching me this.

  18. Gamz says:

    Hui - Excellent Tip! Will start using it immediately. Up to now, I had been using the technique of white colored fonts to hide what I did not want displayed - this is much better. Thanks

  19. Ninad Pradhan says:

    this is great.

  20. Jon Peltier says:

    #,##0,K
     
    This works in 2003, 2007, and 2010 for axis labels. In 2007 it doesn't seem to work at first, because the stupid dialog makes you click the stupid Add button before it acknowledges the new custom format.

  21. Ghazanfar J says:

    thanks hui and jon. you guys never cease to amaze me.

  22. Tom Quist says:

    @Jon - I'm still not getting it to work properly in 2007. I used the add button and everything, lol. However, as I stated, it seems to work properly when I use:

    #,##0K

  23. Jon Peltier says:

    Tom -

    Al I can think of is that you haven't applied all available service packs to Office 2007. We're up to SP2 by now (or did SP3 come out already?).

  24. Tom Quist says:

    Hmmm...I'll check it out. Anyways, thanks for your feedback, Jon and Hui.

  25. Hui... says:

    @Jon
    When faced with the choice of colors for the charts
    A) Use Windows 2003 Default Color
    B) Use a single color/style throughout
    C) Use a variety of colors/styles

    I Choose C), if for no other reason than putting some color into the post.

  26. Jon Peltier says:

    Hui -
     
    Yeah, color and variety are nice. But don't go using distracting gradients or patterns, because if people see it on this site, they'll think it's okay.
     
    Variety: Use a different fill color for each chart (not each data point), use different chart types (line, XY, column, bar, area).

  27. lavkesh bhatia says:

    Great tip !

    In the Indian scenario, numbers frequently needs to be shared in lacs

    so using ## ## ### is a nifty way to format it in lacs if you don't intend to divide it

  28. Lucas says:

    Hi
    I would like to know if here is a way of making a conditional formatting on a chart?
    For example, you have a bar chart, and I want that the ones that are bigger than 100 been colored on red, and the ones that doesn't in blue.
    It's possible?

    Thanks

  29. Hui... says:

    @Lucas
    Yes, this can be done
    You use 2 series instead of 1
    Assume your data labels are in Column A1:A10
    You data is in B1:B10
    .
    In the first series have your value <100 or Na()
    C1: =If(B1<100,B1, na())
    .
    In the second series have your value >=100 or Na()
    D1: =If(B1>=100,B1, na())
    .
    Now plot the 2 Columns C&D on a chart as a Cumulative Column Chart

  30. Ram says:

    Seriously, Do you have any PDF File about Excel.I have to learn.If you have please send it to my email bharatmax at yahoo dot com.yahnk you.

  31. Prem Sivakanthan says:

    I only stumbled across custom number formats recently, but you can do some pretty cool stuff with them. Some of my spreadsheets need to be compatible with 03 - my favorite custom number format trick is to use the up and down arrows as part of the format to signify growth or decline compared to the previous period like: ? 0%;? 0%;;
    of course you can use 07's conditional formatting to do this, but this is just as cool...!

  32. Prem Sivakanthan says:

    opps, the question marks above are the up arrows and down arrows

  33. Hui... says:

    @Ram
    Chandoo has lots of online tutorials and examples online
    Start at http://chandoo.org/wp/
    In the right column there is a section devoted to New To Excel ?
    have a browse through there.

    Chandoo also runs a School for Excel Newbies
    http://chandoo.org/wp/excel-school/

    Chandoo alos has an E-Book which covers the most common/used functions
    http://chandoo.org/wp/excel-formula-helper-e-book/

    Use the Google Search at the Top Right of all Chandoo's screens
    to search for specific topics or Excel Functions.

    Start small and work up
    Get the basics right and work out from there
    Don't worry about VBA for another year

  34. [...] week I introduced the technique of using custom Number Formats for Chart Axis Labels which was well [...]

  35. Yard says:

    I seem to recall reading about number formats increasing a file's size and perhaps being "expensive" in terms of processing. Good practice suggested keeping custom number formats to a minimum.

    Does that ring any bells with anyone?

  36. Shair says:

    I love this - but does anyone know how to clear the unwanted custom formats from the Type box? I have played with it a lot, and now I have so many that just don't make sense. I would like to remove them. Is this possible? Thanks!!

  37. Al says:

    Hi Chandoo:

    I just came across your website and find it useful and intriguing.

    Not being a "power" excel user, I was wondering if you knew how I might be able to create a fuel sales chart (using a bar chart), with a fuel sales budget (using, for example, a line) superimposed on the same bar chart..?

    In other words combining two chart types in one chart.

    Appreciate any thoughts you might have to share on this item.

    Thank you.

    Al Gulamali

  38. Jessica says:

    Hi! I am trying to use the different colors to mark off standards on a line chart. So if the standard is 6 I would want 6 to be blue. I followed the steps and it works great, but what if I wanted it linked to a certain cell. The standards change according to month so can i have the numbers change color on the axis with them linked to a cell in the sheet? Thanks!

  39. Yau says:

    Hi HUi,

    i need my value auto matic go to k and M..
    example: 3000=3k
    3000000=3M

    any formating code?

  40. John says:

    Is there a way of making the highest value in the axis any particular colour and where this axis max value is set to automatic?

  41. Andre Terra says:

    Great post! I knew to use Number Formatting everywhere (including Data Labels), but this post gave some food for thought. I particularly loved the idea of using Webdings... I am actively looking for a reason to use them and befuddle everyone in the office.

    Meanwhile, I can't seem to do use this method for category axes -- this is actually how I landed here from Google. Is it possible at all or do I have to resort to something else?

    • Hui... says:

      @Andre
      I don't think there is any issues applying these techniques to Category Axis
      Can you post a sample file with instructions on what you require

  42. Exige says:

    Any idea how to multiply an axis number?

    Eg value = 0.2 * value

Leave a Reply