Chart with two different scales on the secondary y axis?

[ChrisXcel]

Is this possible?
A search hasn't turned anything up yet.

To be clear. There are three sets of data plus dates that go along the x axis.

Data1 is plotted and its y axis on the LEFT side shows its scale, say from 0-7000.
Data2 is plotted and its y axis (secondary) on the RIGHT side shows its scale, say from 0-4000.
Data3 is plotted and its y axis (secondary) on the RIGHT side shows its scale from 0-100.

The reason I would like two different y axis scales is because if just one was shown,
Data3 would not show anything meaningful (it would look like a flat line at the bottom).

Have I stumped Excel or is this possible?
Thanks!

[FDibbins]

Nope, you can only have 1 scale per axis.

A work around might be to increase the 2nd set of values by a factor of 40

[Andy Pope]

You will need to 'draw' the extra axis yourself.
But consider the alternatives suggested on Jon's page.

https://peltiertech.com/Excel/Charts/TertiaryAxis.html

[ChrisXcel]

Nope, you can only have 1 scale per axis.

A work around might be to increase the 2nd set of values by a factor of 40

Thanks for helping!

I'm really surprised that we're now in 2021, and Excel hasn't added this basic feature!
It would be easy to create two columns on the right or left side of the chart, with
different scales.

Let's see if we can possibly work with your 'work around'.

I will show you the graph creation code that I now have below.
For me the tricky part would be to adjust the multiplication factor according to
the data for each location.

For example for location A, the factor might be x40.
For location B, the factor might be x10.
etc.

So first I would need code to check through the data for each location that will be charted,
find the biggest number in Col B for that location, say it was 3200, and then multiply the
numbers in Col D by a factor of perhaps 20 (Col D will always be between 0-100).

Please Login or Register  to view this content.

[ChrisXcel]

You will need to 'draw' the extra axis yourself.
But consider the alternatives suggested on Jon's page.

https://peltiertech.com/Excel/Charts/TertiaryAxis.html

Thanks Andy for helping!

I checked out Jon's page and the charts he came up with look great!
The tricky part would be to implement them for my case.

He went through many complicated steps to arrive at his final charts.

I actually liked his first chart, which he discarded because he thought it
looked too "cluttered and confusing".

https://peltiertech.com/Excel/Charts/TertiaryAxis.html

Would you know how to create that 'tertiary axis' example at the top?

[Andy Pope]

I would think the simplest approach would be to determine the min and max of each axis and then refactor each set of data as a percentage of the scale spread.
The actual vertical axes would be additional data series with data labels used to show the scale value.

[ChrisXcel]

I would think the simplest approach would be to determine the min and max of each axis and then refactor each set of data as a percentage of the scale spread.
The actual vertical axes would be additional data series with data labels used to show the scale value.

Thanks Andy.
Would you be able to give me an example for a simple set of data?
I could then use it as a model to create all of my charts.

[MrShorty]

I found this tutorial: https://engineerexcel.com/3-axis-graph-excel/

[ChrisXcel]

I found this tutorial: https://engineerexcel.com/3-axis-graph-excel/

Thanks MrShorty! Nice find.
That's exactly the look that I am after.

The problem is that this is not for a one-off chart.

I am using VBA code to create more than 100 charts on a daily basis.
And each chart has different axis requirements.
One of the extra axes may have a range from 0-400,
while the next may be 0-2000, and the next 0-5000, etc.

The VBA code would first need to check the data for each site,
find its axis range, then tailor the chart accordingly.

[MrShorty]

I'm not sure why "creating multiple charts using VBA" should be a major impediment to this. Maybe I'm not fully understanding how you intend to interact with the spreadsheet and the data, but I would break this down into stages (divide and conquer).

1) Did you follow the tutorial to manually create one chart? This is mostly about understanding how the proposed spreadsheet template comes together to create the data for the chart and then create the chart from that data. This also gives you a chance to begin thinking about the thought process (algorithm) you want to use to choose scaling factors and other parameters that go into building the dummy axis (in the tutorial, he kind of pulls those numbers out of the air. You will need something that you can code into the spreadsheet and/or VBA).

One big advantage I see to taking the time to build the template is that, as he explains at the end, once you have the template build and understand how it works, building new charts with new data is a "simple" matter of putting new data in and changing the scaling factor and a couple of other things in the spreadsheet. I don't know how you like to do this, but I like to design something like this so that the spreadsheet template is completely or nearly "self-contained." If I can successfully do that, then the VBA loop part needs only past the new data into the template then grab the chart and send it off to where it needs to go.

2) Once you have a spreadsheet template that can build one chart, then figure out what you need VBA to do. Do you want/need VBA to recreate everything you just did to build the spreadsheet/chart? Or did you design the spreadsheet+chart so that VBA need only paste new data in. As noted, I like to make my spreadsheets "self-contained" so that this VBA programming part is minimal.

3) Once you have gone through 1 and 2, then it is just a matter of building the VBA loop that will contain those programming "modules".

Beyond that, I'm not sure what specifically to recommend. You are correct that there are some aspects of this that still need to be figured out (like how to choose scaling factors). If you can help us understand the algorithms and thought processes behind these problems as well as a better understanding of you like these things programmed and how you intend to interact with the spreadsheet and VBA and all of that, we should be able to help with more details.

[ChrisXcel]

I'm not sure why "creating multiple charts using VBA" should be a major impediment to this. Maybe I'm not fully understanding how you intend to interact with the spreadsheet and the data, but I would break this down into stages (divide and conquer).

OK thanks for the strategy tips MrShorty. Yes I was able to follow the tutorial.

I will try to build a template chart and see how it goes.

Although it would seem like simpler to just have VBA code paste new data in and loop for the hundred odd
charts already created, I don't have existing charts.

The code I am using deletes all charts to start, then takes fresh data and uses that to create new charts from scratch.