Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Charting / December 2003

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Robert Noble - 15 Dec 2003 19:21 GMT
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
Tushar Mehta - 15 Dec 2003 23:36 GMT
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.

Signature

Regards,

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

> Now this question will probably give you a clue as to my level of understanding of Excel.
>
[quoted text clipped - 10 lines]
> Thanks in advance
> Robert
Robert Noble - 16 Dec 2003 11:06 GMT
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
Tushar Mehta - 16 Dec 2003 15:41 GMT
Don't know why it doesn't work for you.  Try: copy the worksheet range,
select the chart, and use Edit | Paste Special...

Signature

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?
[quoted text clipped - 8 lines]
> Thanks for your help
> Robert
Jon Peltier - 16 Dec 2003 16:15 GMT
Robert -

I have a worked out example on my web site:

 http://www.geocities.com/jonpeltier/Excel/Charts/Y_CategoryAxis.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

> 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?
[quoted text clipped - 8 lines]
> Thanks for your help
> Robert
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.