Chart with two different scales on the secondary y axis?

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!

Originally Posted by 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

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).

Sub makechart(title1 As String, SourceData As String)
    Dim ChartSheet As Worksheet
    Set ChartSheet = ThisWorkbook.Worksheets("Charts")
    
    Dim iChtIx As Long
    iChtIx = ChartSheet.ChartObjects.Count + 1
    Dim cht As Chart
    
Set cht = ChartSheet.Shapes.AddChart2(XlChartType:=xlColumnClustered, Width:=MyWidth, Height:=MyHeight, Left:=((iChtIx - 1) Mod NumWide) * MyWidth, Top:=Int((iChtIx - 1) / NumWide) * MyHeight).Chart
     
    With cht
        .SetSourceData Source:=Range(SourceData), PlotBy:=xlColumns
           
        .FullSeriesCollection(1).ChartType = xlLine           
        .FullSeriesCollection(2).ChartType = xlColumnClustered    
        .FullSeriesCollection(3).ChartType = xlLine             
        
     
         .SeriesCollection(1).Name = "=""Data1"""
         .SeriesCollection(2).Name = "=""Data2"""
         .SeriesCollection(3).Name = "=""Data3"""
        
                
         cht.HasLegend = True
         cht.FullSeriesCollection(1).Select
         ActiveChart.FullSeriesCollection(1).Select
         ActiveChart.FullSeriesCollection(1).AxisGroup = 2
                
         cht.FullSeriesCollection(3).Select
         ActiveChart.FullSeriesCollection(3).Select
         ActiveChart.FullSeriesCollection(3).AxisGroup = 2
                
              
                
                
        .HasTitle = True
        .ChartTitle.Characters.Text = title1            
        .Axes(xlCategory, xlPrimary).HasTitle = False  
        .Axes(xlValue, xlPrimary).HasTitle = False       
        
        Call EnableChartEvents(cht)
    
   
    End With


End Sub

Originally Posted by 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

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?

Originally Posted by 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.

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.

Originally Posted by MrShorty

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.

Originally Posted by 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).

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.