1

Is there a way to set the scale on a chart so that the primary maximum and minimum Y-values equal the same values on the secondary Y axis as well?

Basically I have a number of different stocks I'm looking at (historical log values), which each has its own line graph of the prices, and then I added a secondary series that shows 3 horizontal lines - the average price, and one standard deviation above and below the average so I can see the usual price range the stock fluctuates in (this series I have as a Scatter graph). My problem is that Excel seems to be using a different auto-scale for each Y axis (I've tried including the same minimum and maximum values in my secondary series, but the scale is still different sometimes).

Ideally, since I'm looking at quite a few different stocks, I'd like to automate this by adding a few columns where I could just set the maximum and minimum Y-values to equal the maximum or minimum price of each stock (for both the primary and secondary axes). I tried looking at the following site to see if I could create a macro (http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html) but my macro-writing skills are pretty deficient and haven't had much luck.

I realize this request might only be possible through VBA though, so basically long story short, would anyone know how to set up a macro that will enable my selected chart to autosize using the parameters I give it? (Say column A shows the dates, B shows the prices, and a section to the right (say cells D1:F4) would specify the Min and Max values of X and Y, and maybe the Tick size that the macro could read from? (I know how to create a blank macro, and then jump into the macro itself, but that's where I start getting a bit lost as to where to actually paste the code).

  • 1
    Does this not mean that the Y axis equals the Y(2) axis? If so, why have a secondary axis? – mcalex May 12 '14 at 4:22
  • Hi mcalex, the reason I needed the secondary axis is so I could show the price range over time on the first axis, and then take the average, and one standard deviation above and below the average so I could add 3 horizontal lines on top of my chart so I could see where the stock is at in relation to its prior history. It just makes easier for me to see where it's currently trading in relation to it's normal range over time. My problem was Y1 and Y2 were using different scales, so the data did not match up. The macro dav created below worked perfectly to automate this fix for me. Thank you tho – steveblank May 18 '14 at 19:54
0

This code snippet should provide you the basics for your request. In a module for your workbook, it should stand alone, or from here you can expand it to other worksheets or to modify any part of a chart. The first couple lines are for error trapping-in case you don't have a chart selected. Everything else formats. Just point the reference ranges to the cells you'd like to use for your scales.

Sub CustomAxis()

If ActiveChart Is Nothing Then ' Error trap to ensure a chart is selected
    MsgBox "Please: Select a chart and try again.", vbExclamation

    Else

        ActiveChart.Axes(xlValue).MinimumScale = Range("A2") 'Set min scale to cell A2
        ActiveChart.Axes(xlValue).MaximumScale = Range("A3") 'Set max scale to cell A3
        ActiveChart.Axes(xlValue).MajorUnit = Range("A4") 'Set major tick to cell A4
        ActiveChart.Axes(xlValue).MinorUnit = Range("A5") 'Set minor tick to cell A5

End If

End Sub

  • Perfect! Thank you so much dav, I appreciate it. This worked great. – steveblank May 18 '14 at 19:43
0

You could make this automatic and independent of VBA by adding some data to the chart.

In cell F2 (for example) insert a formula that computes the minimum of all of the Y values on both axes, and in F3 insert a formula that computes the corresponding maximum. Like this:

Equal Axis Scales - Sample Data

The first chart below shows just the stock data, the second shows the stock data on the secondary axis with the reference lines on the primary axis (so the ref lines appear behind the stock data).

In the third chart I've added the min/max data from column F to the primary axis, and in the fourth to the secondary axis.

Finally in the fifth chart, I've formatted both min/max series without lines or markers so they are hidden. You can remove them from the legend if necessary: click once on the legend, then once on the legend entry to delete, then click Delete.

However the data changes, the min/max calculations in column F will update, and so will the chart.

Equal Axis Scales - Evolution of the Chart

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.