Selective Chart Axis Formating

From chandoo.org

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

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…