MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Words on Scaled Chart Axis


Posted by Andrew Watson on January 07, 2002 11:11 PM

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.

Andrew Watson


Posted by Mark W. on January 08, 2002 7:11 AM

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.