How can change the 'Y' or Secondary axis to a non numeric value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Now this question will probably give you a clue as to my level of understanding of Excel.

I have to produce (by the morning) a line chart along the following lines
Theme July Aug September October
Nurse Probable Likely Possible Remote
Factory worker Probable Probable Remote Remote
Teacher Possible Probable Remote Possible
Mechanic Possible Possible Possible Possible

I can produce a chart that with the ‘Month’ along the ‘X’ axis and the ‘Legend’ gives me a separate colour for the various groups of people but the 'Y' axis is a numeric value.
What I can’t seem to do is find out how to set the values of the ‘Y’ axis to the 4 values of ‘Remote; Possible, Probable; Likely’. Excel automatically sets the Y axis to a numerical value and I can’t see how to overcome it.
Can anyone help?
Thanks in advance
Robert
 
Here's how you can do what you want:

Replace the text by numbers using a mapping scheme such as:

Remote 1
Probable 2
Possible 3
Likely 4

Put the above in some worksheet range.

Plot the graph using these numbers such that the months are along the
x-axis and the Theme forms the legend.

Next, create the data set:

0 1
0 2
0 3
0 4

Add this to the graph. To do so, select the above range and drag onto
the chart. In the resulting dialog box, ensure you check 'New Series'
and 'Data in Columns'.

Select this new series. Select Chart | Chart Type... and select XY
Scatter chart. XL will automatically add a secondary x- and y- axes.
Double click the secondary y-axis. From Patterns set everything to
None. From the Scale tab, set Major Gridlines to 1 and Max. value to
5. Format the secondary x-axis to show no pattern information. For
the primary y-axis, set the Axis labels to None (double click the axis,
then select the Patterns tab).

If you don't already have Rob Bovey's add-in, XY Chartlabeler, get it
from www.appspro.com. Use it to add the labels to the dummy series
added above. Put the labels to the left.

Adjust the Plot area so that the labels show up correctly. Select the
legend. Pause, then select the legend for the dummy series. Delete
it.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Many thanks for the quick response.
I am havin a little problem, probably caused by by lack of knowledge using Excel. Can you please help with?
I'm ok upto plotting the graph with the months on the x-axis and the Theme shown in the Legend.
I can't seem to get the next bit. I have 2 colums as follows
0 1
0 2
0 3
0 4
When I select and drag these colums onto the Chart, the 0's appear in the Legend, but I don't get a dialog box pop-up.
It's proving to be a more complicated task than I first thought. Where am I going wrong?
Thanks for your help
Robert
 
Don't know why it doesn't work for you. Try: copy the worksheet range,
select the chart, and use Edit | Paste Special...

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top