Mirroring primary axis on a plot

[vbacoder]

Hi everyone,

I would be grateful if anyone could let me know if there is a way to mirror the primary axis (including scaling) so it apperars on both left and right sides (for y axis) of a plot. I remember there is a way to do this, but I don't know if I was dreaming at the time, as I cannot for the life of me figure out how to do this.

Many thanks,

vcoder

 

[Jon Peltier]

The mirrored axis is the secondary axis. You need a second series in the chart, so if you don't already have two or more series, add the original data as a second series. Then double click teh new series, and on the Axis tab, choose Secondary. Then format the secondary axis to match the primary axis.

 

[vbacoder]

Hi Jon,

Thanks for your reply. I don't think I've explained this very well. I am using both primary and secondary axes. I just want to constrain the scaling on the secondary to follow that on the primary when the data within the assigned charting fields changes.

The series assigned to the primary are all required for a box plot that needs 4 data points to define each box. I then want to superimpose a 5th data set (or series) onto the chart, without having it alter the nature of the box plot. Even if I tell the charting wizard that the 5th series is a new series, it alters the original 4-data set box plot. So, what I do is assign the 5th set of data to the secondary, and this seems to work in that it leaves the box plot untouched. I can set the scaling on the secondary manually so that it matches that on the primary, however, when the source data changes, the scaling on each moves independently.

So, my problem is that I want the two axes to autoscale in unison, without manual intervention on my part.

I would be very grateful for any suggestions.

Many thanks,

vcoder

 

[Jon Peltier]

Add another series to the secondary axis. This series has two points, one being the minimum of all points plotted on the primary axis, the other the maximum. These are the values Excel uses to autoscale its axes, and with the same min and max on primary and secondary axes, the scales on the primary and secondary axes will be the same. Format this new series with no lines or markers to make it invisible.

 

[Jon Peltier]

Alternatively, if you're using four line series with the high-low lines and up-down bars to construct your box plot, adding another line series will muck up the up-down/high-low features, but you could add the series as an XY series, using 1, 2, 3, for the category (X) values, and assigning it to the primary axis.

 

[vbacoder]

Add another series to the secondary axis. This series has two points, one being the minimum of all points plotted on the primary axis, the other the maximum. These are the values Excel uses to autoscale its axes, and with the same min and max on primary and secondary axes, the scales on the primary and secondary axes will be the same. Format this new series with no lines or markers to make it invisible.

Hi Jon,

I like this trick! I tried it and it does the job. Thanks very much for getting back to this thread with this and the other suggestion.

Many thanks indeed!

vcoder