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…
51 Responses to “Selective Chart Axis Formating”
This is really new an unexplored by me.. i guess 8yrs+ working on excel is still nothing 😉
Cheers
Rohit1409
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
Wow, talk about timely help... I've been struggling with this all week. Thanks!
Nice post Hui. I'd known about using custom formats in cells, but I never thought to use them in charts. =)
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?
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.
@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
12,345,100 = 12,345K
Try 0,000,K
Bristly? Yeah, my wife wishes I'd shave more regularly...
@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
@Jon - I just chortled. Sorry if I offended - I could have left the bristly part out. That's what she said - oh, snap!
@Tom
[>1000000]0,000,K;[>1000]0,000;0
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.
@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.
@Jon - BTW, I understand the plain text conundrum. That's why Al Gore included emoticons when he invented the internet 🙂
like it too much, thanks..
This is very cool indeed. Thanks Hui for teaching me this.
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
this is great.
#,##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.
thanks hui and jon. you guys never cease to amaze me.
@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
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?).
Hmmm...I'll check it out. Anyways, thanks for your feedback, Jon and Hui.
@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.
[...] Chandoo.org [...]
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).
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
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
@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
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.
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...!
opps, the question marks above are the up arrows and down arrows
@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
[...] week I introduced the technique of using custom Number Formats for Chart Axis Labels which was well [...]
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?
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!!
[...] http://chandoo.org/wp/2011/08/19/selective-chart-axis-formating/ [...]
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
@Al
The type of charts you refer to are called combination charts
Start having a look here: http://chandoo.org/wp/2009/01/05/excel-combination-charts/
[...] http://chandoo.org/wp/2011/08/19/selective-chart-axis-formating/ [...]
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!
[...] Chart Axis formatting – Part 1 & Part 2 [...]
Hi HUi,
i need my value auto matic go to k and M..
example: 3000=3k
3000000=3M
any formating code?
@Yau
[<1000] 0;[<1000000] 0,"K"; #,,"M"If you copy/paste this you may have to retype the quote marks " manually
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?
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?
@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
Any idea how to multiply an axis number?
Eg value = 0.2 * value
@Exige
You can multiply/divide Number Formats by factors of 10 but not by other numbers
Argh I guess ill take an alternative approach. Thank you!