Mirroring primary axis on a plot

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
Jul 7, 2007
Messages
354
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
Jul 7, 2007
Messages
354
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,416
Messages
5,596,010
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top