Secondary y-axis, column

[tripptofer]

Hiya-

I just learned that you can have a 2nd y-axis on a column chart, which I am told is more appropriate for categorical data than a line chart.

Problem is that I only seem to be able to put 2 columns of data on the secondary ordinate- there seems to be a limit of 2 (when I try and put the third set on the 2nd y the fields are fuzzed out).

Is this a limitation/bug of excel or pilot error?

Thanks!

 

[chergh]

Sounds like pilot error but I'm not really able to understand the problem from your description. Uploading the worksheet may help.

 

[tripptofer]

Ok- I had another stab at it. I was originally trying from scratch and after reading your reply, I converted a line graph that worked into columns.

Either way however, it would seem that excel is hiding one of the series- even after tweaking the gap/overlap.

Not sure if this is an excel eccentricity or if I'm still not privy to something...

Thank you for responding.

Regards,
tripp

 

[chergh]

You do realise that have two sets of column charts on a double axis is a really bad idea as it makes the graph confusing as hell. Use a column chart for one axis and a line chart for the second axis, loks better, esier to understand and it solves your issue.

 

[tripptofer]

Can you believe this was my attempt to address a reviewer's comment for a journal paper? I thought 2 lines would be fine- something to do with continuous variables, which I thought time was.

Apart from aesthetics, is there a way to fix this problem apart from the workaround of setting up dummy empty series?

My next issue was getting my head around calculated items and fields- I don't suppose you au fait with these as well? I've been slowly learning more and more over time and keep stumbling upon better ways of doing things and have a sneaking suspicion that there are yet another example...

Thanks again,
tripp

 

[Brianwarnock]

I don't know what has happened but other public funds are stacked on other public grants.

Brian

 

[chergh]

The issue is that when you use a double axis the columns occupy the same space, quite commonly on column/line double axis the two series will be related and you will want the line point to appear in the center of the related column.

I don't have an answer of the top of my head or the time to look at it but I would recommend posting your question at:

http://www.excelforum.com/excel-charting/

They have some real chart geniuses but are very strict about their rules, make sure you read them before posting.

 

[chergh]

Right here is the best i can do. The explanation for what I have done can be found at http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html

 

[tripptofer]

I'm grateful for kind assistance and agree that the dbl y cols. aren't pretty.

Thank you for this- I'm now itching to contact the reviewers to see what exactly they had in mind with their comment...

Cheers!