Some days ago, while I was checking the discussions on an Excel-related group on LinkedIn, I bumped into an interesting question about charts. The question was “how to automatically set the secondary Y axis scale via code so that both primary and secondary axes share the same number of gridlines”.
I thought this question was interesting for two reasons: first, it’s quite common to include a secondary Y-axis on a chart, just to display another series that has a different scale than the first one. In the COPERT Micro tool, for example, I have many charts that show emissions (primary Y) and the number of vehicles (secondary Y) as a function of the hour of the day (X-axis). Second, the question reminded me of a similar need that I had some years ago. So, I found the code that I had written then (2012) and I made some minor changes in order to make it more generic, and, here it is!
The manual way
By the way, it’s not hard to adjust the scale of the secondary Y axis manually. Just follow the instructions that follow (for Excel 2013).
Step 1: Set a data series to be plotted on the secondary axis:
- Select a series on the chart by right-clicking on it.
- On the pop-up menu, select Format Data Series.
- On the Format Data Series menu that will appear, select the Series Options tab.
- Select the plot on the secondary axis radio button.
Step 2: Adjust the scale of the secondary Y-axis:
- Select the secondary Y axis by right-clicking on it.
- On the pop-up menu, select Format Axis.
- On the Format Axis menu that will appear, select the Axis Options tab.
- Finally, set the desired values on Minimum/Maximum Bounds and on Major Unit text boxes.
In the last step, you might need to make some (repeating) tests on the values that you will enter in the 3 text boxes (especially in the Major Unit text box) in order to achieve that both primary and secondary axes will share the same number of gridlines.
VBA code for adjusting the secondary Y-axis
And here is the “easy way”; no need to test the values on the text boxes, no worries about how the axes will look uniformly. It’s a short macro that can be customized according to your needs.
Option Explicit Sub AdjustSecondaryYAxisScale() '-------------------------------------------------------------------------- 'Automatically adjusts the scale of the secondary Y axis, so that both 'primary and secondary Y axes share the same number of (major) gridlines. 'Written By: Christos Samaras 'Date: 14/09/2012 'Last Update: 18/04/2015 'E-mail: [email protected] 'Site: http://www.myengineeringworld.net '-------------------------------------------------------------------------- 'Declaring the necessary variables. Dim ch As Chart Dim Ymin As Double Dim Ymax As Double Dim Yscale As Double Dim Ylines As Integer Dim sYmin As Double Dim sYmax As Double Dim sYscale As Double 'Set the ch variable to a specific chart on sheet 1 (in this example). 'If you need to set the ch variable to active chart you can use the next line: 'Set ch = ActiveChart Set ch = Sheet1.ChartObjects(1).Chart 'A quick test if the ch variable is not empty. On Error Resume Next If ch Is Nothing Then MsgBox "The chart wasn't set!", vbCritical, "Empty Chart" Exit Sub End If On Error GoTo 0 'Set the minimum/maximum bound and the major unit to Auto for both primary and secondary axes. 'For the primary Y axis this is NOT always necessary, so the corresponding 3 lines can be deleted/commented. With ch .Axes(xlValue).MinimumScaleIsAuto = True .Axes(xlValue).MaximumScaleIsAuto = True .Axes(xlValue).MajorUnitIsAuto = True .Axes(xlValue, xlSecondary).MinimumScaleIsAuto = True .Axes(xlValue, xlSecondary).MaximumScaleIsAuto = True .Axes(xlValue, xlSecondary).MajorUnitIsAuto = True End With 'Get the mininmum bound of the primary Y axis. Ymin = ch.Axes(xlValue).MinimumScale 'Get the maximum bound of the primary Y axis. Ymax = ch.Axes(xlValue).MaximumScale 'Get the major unit of the primary Y axis. Yscale = ch.Axes(xlValue).MajorUnit 'Calculate the number of major gridlines. Ylines = Round((Ymax - Ymin) / Yscale) 'Get the mininmum bound of the secondary Y axis. sYmin = ch.Axes(xlValue, xlSecondary).MinimumScale 'Get the maximum bound of the secondary Y axis. sYmax = ch.Axes(xlValue, xlSecondary).MaximumScale 'Note that you can easily set the minimum/maximum bound of the secondary Y axis to any value you like. 'In other words, you can make your secondary Y axis look exactly as you want. 'For example if you uncomment the next 2 lines, the first gridline of the secondary Y axis will start at 0 and 'the last one will end at the value of 30. The number of gridlines will be automatically calculated/adjusted. 'sYmin = 0 'sYmax = 30 'Calculate the new major unit of the secondary Y axis. sYscale = Round((sYmax - sYmin) / Ylines) 'Calculate the new maximum bound of the secondary Y axis. sYmax = sYmin + Ylines * sYscale 'Set the minimum/maximum bound and the major unit of the secondary Y axis to their new values. With ch.Axes(xlValue, xlSecondary) .MinimumScale = sYmin .MaximumScale = sYmax .MajorUnit = sYscale End With 'Release the chart object. Set ch = Nothing End Sub
In the workbook that you will find in the downloads section below, you can try the above code by experimenting with a chart that I created using random numbers. Just see how nice the scale of the secondary Y-axis “follows” the scale of the primary Y-axis.
The file can be opened with Excel 2007 or newer. Please enable macros before using it.