Selective Chart Axis Formating
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
If you setup a fancy Custom number Format as say
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.
You can see how all the above Charts are made using the Sample Data File Attached
The links below will explain the intricacies of Custom Number Formats.
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:
For a list of my other contributions at Chandoo.org please visit: Hui…
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Join Excel Formula Crash Course & Master Excel Formulas||Custom Chart Axis Formating – Part 2. »|