Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Secondary axis, Excel2003 2

Status
Not open for further replies.

lionelhill

Technical User
Dec 14, 2002
1,520
GB
I do all my plotting outside Excel, so I'm stumped with this question that came from a co-worker.

They are plotting vertical bar charts, but one group of data is on a much larger scale than all the remaining data. They therefore want a secondary axis.

I got as far as plotting all data, selecting the set of bars that are unreasonably large, and using format - axis - secondary axis to put them on a different axis on the right hand side.

But now we have the small bars plotted OK, with the large ones plastered indiscriminately across the middle of each cluster of bars, printed in the foreground, obscuring everything else.

Is there a way to keep the large bars in their original position on the x-axis, neatly to the right of the cluster of low-value bars?

Sorry if this is a stupidly trivial question. I'm sure it ought to be ludicrously easy; after all, why bother offering a secondary axis in bar-charts if it gives unreadable results?
 



Hi,

You may need to use a trick. Lest say you have 3 series and one need to have another scale.

Option 1: change the chart type of the series on the sec axis to line of area.

Option 2: plot 3 series on one axis, the third series EMPTY. Plot the other series ALSO with 3, but with the first 2 EMPTY.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 



Hi,

You may need to use a trick. Lets say you have 3 series and one need to have another scale.

Option 1: change the chart type of the series on the sec axis to line or area.

Option 2: plot 3 series on one axis, the third series EMPTY. Plot the other series ALSO with 3, but with the first 2 EMPTY.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Besides Skip's suggestion there is another trick as well.

Please look at my answer in thread68-1582274

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents
 
Thanks both for such rapid and helpful replies.

In case anyone reads this thread in the future, we very much like both the suggestions from Skip and xlhelp, but also came up with a third much more dubious trick in the meantime: add a dummy set of data, use it to make a secondary axis, scale the axis to whatever we want, and set the data to zero so no dummy bars appear. Then take the large-value bars, and scale their actual values down by the ratio of the two axes, while leaving them plotted on the primary axis. This means they are the right size for the secondary axis... but it's really horrible because (a) anyone looking at the spreadsheet will have extreme difficulties working out what we've done (and why: it looks like data manipulation); (b) it's all too easy to alter the axis scaling, and forget to alter the amount by which we divide the data for the large-data bars.

So we're very, very grateful for Skip and xlhelp's much more straightforward methods. You've saved a lot of fiddling around.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top