More on Words on Scaled Chart Axis

On January 07, 2002 I posted the following message:

I have just been asked to chart a satisfaction survey in Excel.

The items surveyed were:

Tangibles
Assurance
Reliability
Responsiveness
Empathy
Overall Satisfaction

Each of these could be rated as:

1 Very Dissatisfied
2 Dissatisfied
3 Neutral
4 Satisfied
5 Very Satisfied

I have calculated the average rating for each item for all projects. Some items could have non-integer values.

I want a bar chart with the items on the horizontal axis and the ratings on the left hand axis. But instead of showing 1, 2, 3, 4, 5 I want to show the rating descriptions.

Does anyone have any suggestions how I might replace the numbers with words?

I thought I might have a look at custom number formats but at this stage I don't know how to do it.
I know I don't want to just write the labels manually. I want the system to calculate the correct position.

I received one reply from Mark W:

Andrew, the Y-axis values can't be replaced with descriptions, and a number format can't be constructed for 5 ratings. I'd recommend that you format your chart's data series to include Data Labels showing their value. Once displayed you can link these labels to a worksheet cell corresponding to each your six categories.

For example, suppose cells A1:B4 contained...

{"Item","Rating"
;"Tangibles",4.5
;"Assurance",4.3
;"Empathy",3.2}

You could enter the formula...

=CHOOSE(ROUND(B2,0),"Very Dissatisfied","Dissatisfied","Neutral","Satisfied","Very Satisfied")

into cell C2 and copy down.

Now just select the Data Label for "Tangibles", type an equal sign (=), and click on cell C1. Repeat this process for each of the 6 categories.

This leaves out the step that you have to right click on one of the bars and select Format Data Series, Data Labels tab, Show label to turn on the data labels so that you can click on each one to carry out the last step in the above procedure.

The technique suggested by Mark W placed the satisfaction ratings on the top of each bar.

http://www.vico1.com/upload/Chart1.gif

Neat but not what I had in mind.

I have worked out how to do it:

In Sheet 1, B1 to G1 enter:

Tangibles, Assurance, Reliability, Responsiveness, Empathy, Overall Satisfaction

In B2 to G2 enter:

4, 4.2, 4, 5, 5, 4.5

In all cells from A4 to A8 enter 0.5.

In B4 to B8 enter 1, 2, 3, 4, 5.

In C4 to C8 enter:

Very Dissatisfied, Dissatisfied, Neutral, Satisfied, Very Satisfied

Highlight B2 to G2 and press F11.

Left click on the legend and press DEL.

Right click on the chart background, select Format Plot Area, Area, None to remove grey background.

Right click on the chart background, select Source Data, Series tab.

Change Values from =Sheet1!$B$2:$G$2 to =Sheet1!$A$2:$G$2.

Change Category (X) axis labels from =Sheet1!$B$1:$G$1 to =Sheet1!$A$1:$G$1.

This adds a blank area to the left of the bars so there is room for the labels to be displayed.

Right click on the left vertical axis and choose Format Axis, Scale tab. Change Maximum from 6 to 5 and remove the tick from Major unit 1.

Right click on the chart background and select Source data, Series tab, Add. From Values highlight =Sheet1!$B$4:$B$8.

Right click on one of the bars of Series 2 and select Format Data Series, Axis tab, Secondary axis.

Right click on the right vertical axis and choose Format Axis, Scale tab. Change Maximum from 6 to 5 and remove the tick from Major unit 1.

Right click on one of the bars of Series 2 and select Source Data, Second category (X) axis labels and change to =Sheet1!$A$4:$A$8.

Right click on one of the bars of Series 2 and select Chart type, XY (Scatter), bottom right sub-type.

Right click on the left vertical axis and choose Clear.

Right click on the right vertical axis and choose Clear.

At this point you have to cheat slightly. You need to use a third party Excel Add-in, Rob Bovey's XY Chart Labeler available from http://www.xl-logic.com/xl_files/addins/xy_labels.zip.

Tools, XY Chart Labels, Add XY Labels, Data Series to Label, Series 2. Select Label Range Sheet1!$C$4:$C$8. Alignment Right.

Tools, XY Chart Labels, Move XY Labels, Data Series to Label, Series 2, Up/Down -10.

There is still a pink line on the left vertical axis. Right click on the pink line and select Format Data Series, Line, Custom, Color, Black to make it merge into the vertical axis.

http://www.vico1.com/upload/Chart1.gif

Quite a long and involved process but it works. I'm not sure why it needs the 0.5 values rather than 0 but by trial and error I found that is what is needed to get it to work properly.

Andrew Watson