In Connect Two XY Series I showed how to use an extra XY series to connect two existing XY series in a chart. This shows how points in the first series may have changed to produce the data in the second series. A reader asked in a comment how to use arrows to more visually show the direction of change.
In Excel 2007 drawing these arrows is easy, because one of the formatting changes allows the lines in a chart series to be formatted as arrows. When used judiciously, that is rarely and only for truly informative purposes, the features I and others have derided as “eye candy” can actually be used for good, not just for evil.
In Excel 2003 and earlier, getting these arrows is not as easy, but I have provided a means to do so, and in fact the chart above was produced in Excel 2003.
For users of Excel 2003 and earlier, there are two options for getting arrows to connect the XY points. One option involves constructing complicated sets of data points for the connecting lines, which when connected take on the appearance of data points. This is tedious, especially given unequal X and Y scales and orientation of the line segment. If the chart changes in any way, it is likely that the extra data point will have to be recomputed.
The second option involves drawing autoshapes, line segments with arrowheads, from one point to the other. These look nice, but they are tricky to align with the data points, and if the chart changes, they will have to be realigned. This is a tedious process, but I have covered precision drawing of shapes on charts in Line and Fill Effects in Excel Charts Using VBA on my web site. I have made minor modifications to one of the procedures in that tutorial to allow easy drawing and redrawing of the arrows connecting data points in two series.
To get connecting arrows, create the XY chart with two series. The arrowhead is on the end of the segment at series two, so make sure the series are plotted in the proper order. Then select the chart and run the following VBA procedure.
Note: The procedure has limited error checking. It checks that a chart has been selected, that there are two series, and that the two series have matching numbers of points. It does not check for either axis being potted in reverse order (high value to low), nor for any of the other hundred things that could go wrong.
Sub ConnectTwoXYSeries() Dim myCht As Chart Dim mySrs1 As Series Dim mySrs2 As Series Dim Npts As Integer, Ipts As Integer Dim myBuilder As FreeformBuilder Dim myShape As Shape Dim Ishp As Long Dim Xnode1 As Double, Ynode1 As Double Dim Xnode2 As Double, Ynode2 As Double Dim Xmin As Double, Xmax As Double Dim Ymin As Double, Ymax As Double Dim Xleft As Double, Ytop As Double Dim Xwidth As Double, Yheight As Double ' a chart with two series must be selected If ActiveChart Is Nothing Then GoTo ExitSub End If If ActiveChart.SeriesCollection.Count < 2 Then GoTo ExitSub End If Set myCht = ActiveChart Set mySrs1 = myCht.SeriesCollection(1) Set mySrs2 = myCht.SeriesCollection(2) Npts = mySrs1.Points.Count ' two series must have matching numbers of points If mySrs2.Points.Count <> Npts Then GoTo ExitSub End If ' remove any old connecting arrows For Ishp = myCht.Shapes.Count To 1 Step -1 If Left(myCht.Shapes(Ishp).Name, 12) = "ArrowSegment" Then myCht.Shapes(Ishp).Delete End If Next Xleft = myCht.PlotArea.InsideLeft Xwidth = myCht.PlotArea.InsideWidth Ytop = myCht.PlotArea.InsideTop Yheight = myCht.PlotArea.InsideHeight Xmin = myCht.Axes(1).MinimumScale Xmax = myCht.Axes(1).MaximumScale Ymin = myCht.Axes(2).MinimumScale Ymax = myCht.Axes(2).MaximumScale For Ipts = 1 To Npts ' first point Xnode1 = Xleft + (mySrs1.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin) Ynode1 = Ytop + (Ymax - mySrs1.Values(Ipts)) * Yheight / (Ymax - Ymin) ' second point Xnode2 = Xleft + (mySrs2.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin) Ynode2 = Ytop + (Ymax - mySrs2.Values(Ipts)) * Yheight / (Ymax - Ymin) ' draw connecting line Set myShape = myCht.Shapes.AddLine(Xnode1, Ynode1, Xnode2, Ynode2) ' name and format shape as arrowhead With myShape .Name = "ArrowSegment" & CStr(Ipts) With .Line ' USE YOUR FAVORITE FORMATS HERE .ForeColor.SchemeColor = 12 ' blue .EndArrowheadLength = msoArrowheadLong .EndArrowheadWidth = msoArrowheadWidthMedium .EndArrowheadStyle = msoArrowheadTriangle End With End With Next ExitSub: End Sub