Multiple Plot areas in one chart area

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a multiple line chart that uses both vertical axises where the range changes regularly for each series. I set both axises to the desired setting so that one line (the one that used the left axis) is always on top of the other (right axis). But as soon as I change the source data, the lines go back to overlapping. Is there a way to have one line chart always be on top of the other so that they aren't touching regardless of ranges? I thought of having multiple plot areas within one chart but i didn't see any options for that. thanks in advance.
 
Zac -

You could use a macro to change the axis scales for you. This simple
example squashes the working range of the primary axis into the bottom
half of the chart, and the working range of the secondary axis into the
top half of the chart:

Sub ReSetDualAxes()
Dim i As Integer
With ActiveChart
For i = 1 To 2
With .Axes(2, i)
.MajorUnitIsAuto = True
.MaximumScaleIsAuto = True
.MinimumScaleIsAuto = True
.MajorUnit = .MajorUnit
.MaximumScale = .MaximumScale
.MinimumScale = .MinimumScale
If i = 1 Then
.MaximumScale = 2 * .MaximumScale _
- .MinimumScale
Else
.MinimumScale = 2 * .MinimumScale _
- .MaximumScale
End If
End With
Next
End With
End Sub

- Jon
 
Zac -

After running the code a couple times, I realize a minor change to the
..MajorUnit makes the chart look less cluttered. Whenever you set a new
source data range for the chart, select the chart and run the macro.

Sub ReSetDualAxes()
Dim i As Integer
With ActiveChart
For i = 1 To 2
With .Axes(2, i)
.MajorUnitIsAuto = True
.MaximumScaleIsAuto = True
.MinimumScaleIsAuto = True
.MajorUnit = 2 * .MajorUnit
.MaximumScale = .MaximumScale
.MinimumScale = .MinimumScale
If i = 1 Then
.MaximumScale = 2 * .MaximumScale _
- .MinimumScale
Else
.MinimumScale = 2 * .MinimumScale _
- .MaximumScale
End If
End With
Next
End With
End Sub

- Jon
 
Back
Top