Change Y axes (Categories) on line chart

[Green Biro]

I have a line chart. I am happy with my X axis categories, which say
something like "Term 1", "Term 2", "Term 3" etc
However my Y axis reflects the actual values (eg Points scored) that I wish
to chart, whereas I want it to contain descriptive terms such as "Grade C",
"Grade B" etc
I have read Jon Peltier on how to change axis by creating a dummy series in
a scatter graph. Problem is that if I change to a scatter chart, I lose my
X axis.values that I am happy with.
I suppose that I could then fix this by using Jon's solution twice: once for
x axis and once for y axis but that seems a bit "sledgehammer and Nut". Is
there an easier way just to get the categories on my Y axis without having
to create a completely different type of chart?

Thanks in advance for any assistance

GB.

 

[ShaneDevenshire]

Hi,

You can do it as follows, although I'm not sure if this is any faster than
Jon's method. I am going to assume for this example that you want to display
score equivalents of 20, 30, 60, 80, and 100. And I am assuming your grades
run in that range. After you perfect the technique you can modify it to meet
your needs.

Suppose your data is in the range A2:B13 with title on row 1.

1. Enter the value you want for your lowest grade in C2. For my example I
entered 0 in cell that cell and 20 in D2, then selected both and filled to
the right until H2 which ended with 100.
2. Select the range A1:H13 and insert a line chart.
3. Double click the Category axis and choose the Scale tab, uncheck Value
(Y) axis crosses between categories.
4. Click the Legend, then single click one of the series that has only a
single point on the chart. It should be surrounded by selection boxes. Press
Del. This should remove the series from the Legend but not the chart point.
Repeat this for each of the single point series, until you have only the
grade line left.
5. Choose Chart, Chart Options, Data Labels tab, and check Values.
6. On the chart select one of the data labels for the Grade line and press
Del. Now you have data labels for the points only.
7. Select the top point, not its data label and choose Format, Selected Data
Series and on the Patterns tab change Marker to None. Click OK. The marker
should not be visible.
8. Click the next single point and press F4 - this should also hide this
data point. Repeat for each of the single points.
9. Click the top data label once, then click it once again, there should be
a box around it. Type Grade A and press Enter. This should add the title
you want for the top line. Repeat this for each of the data points.
10. Double-click the Y axis and on the Patterns tab set Tick mark labels to
None.
11. On the Scale tab set the Maximum to 100 and the Major unit to 20.
Click OK.
12. Double-click the first data label and on the Alignment tab set the
Label Position to Left and click OK. One at a time click each of the
remaining data labels and press F4 to move them to the left of the data
points.
13. Click the Plot Area and use the left center sizing handle to size down
the plot area so that the data labels look good.

 

[Green Biro]

Thanks. Looks a bit complicated but I will print out and work through it.
Will reply to group on how I get on.

GB