Connect Two XY Series with Arrows

The Issue

If you have XY data from two different times, or under two different sets of conditions, you can connect points of one series (one time or condition) to another (another time or condition), to help show how the data evolves. For example, here are two series of data that I might want to connect together.

Series A and Series B to be connected by line segments

Several years ago I wrote Connect Two XY Series, which showed how to connect the points of one series to the corresponding points of another series. This approach used a third series with data from Series A and Series B, interwoven with blank cells.

Series A and Series B connected by line segments

I followed this up with Connect Two XY Series with Arrows (VBA), which showed how to connect the two series with arrows. In Excel 2003 and earlier, you had to use VBA to draw the arrows, and this article provided the VBA (and it’s reproduced below).

Series A and Series B connected by arrows, using VBA

This works nicely enough, but the arrows are not tied to the points, but only to the original positions of the points within the chart. So if you change the axes in the chart …

Series A and Series B no longer connected by arrows

… or change the size or shape of the chart …

Series A and Series B no longer connected by arrows

… the arrows no longer match up to the points.

You could always delete the misaligned arrows and rerun the VBA procedure, but what a pain.

I hinted in this second article that you could connect the points with arrows in Excel 2007, without using VBA but instead by formatting the third series that connected the first two series. But I never explained how. This article fills the gap.

Connect Two Series with Arrows, Without VBA

You need to keep the original Series A and B data in place, so you can still easily plot the original points. You need to get data into a new range, interweaving Series A with Series B.

Set Up The Data – Approach 1

To get the data into this alternating arrangement, start by pasting the data from Series A into a new range, then paste the data from Series B below it. Enter a set of index numbers into a column adjacent to this combined data, from 1 to the number of points in Series A, and from 1 to the number of points in Series B (which should be the same number of points). This is the first data block below.

Select the data, and sort by the column of digits you just entered. The data now has alternating Series A and B data (below right).

Sorted Data for Series A and B

Select the data and create a chart, or add it as a new series to the chart of Series A and Series B.

Series A and Series B connected with lines, but needing gaps at alternating segments

Hmmm, that’s almost what I want. But there are too many line segments. We need to eliminate half of them, shown as dashed lines below.

Series A and Series B connected with lines, showing where gaps are needed

We could format alternating line segments to be drawn with no line, but that’s mighty tedious after the first or second segment. Speaking from experience, because I had to format the dashed lines in the chart above, segment by segment.

As I’ve written about in Mind the Gap – Charting Empty Cells, the way we get a gap between points in a chart is to have a gap in the data, that is, blank cells between the points.

So let’s alter our protocol above, so that we start with the Series A data, then the Series B data below that, then an equal number of blank rows at the bottom, and include our index column (below left).

Sort by the index column, to get Series A and B data pairs, separated by blanks (below right).

Sorted Data for Series A and B and blanks

Now when we plot this data, we connect appropriate pairs of points, with gaps between points we do not want to connect.

Series A and Series B connected with lines, with gaps

Set Up The Data – Approach 2

Above we produced our data range by copying the data into a new range, and sorting the data appropriately. We could instead have stretched out the data first, then copied and pasted using the Skip Blanks option.

Here’s a copy of the Series A and B data, with two rows inserted between each point.

Series A and B data, expanded with blank rows

Copy the expanded Series A data, select the first cell of a new range, …

Series A data copied

… then paste.

Series A data pasted

Now copy the Series B data, select the cell below the first Series A point, …

Series B data copied

… and use Paste Special, and check the Skip Blanks option near the bottom of the dialog. Skip Blanks prevents blanks in the copied range from being pasted over data in the target range, so the Series A data is preserved.

Series B data pasted, skipping blanks to preserve Series A data

Now the data is ready to plot.

Make the Chart

Select the Series A data (shaded blue below), and create an XY Scatter chart. Then copy the Series B data (shaded orange), select the chart, and use Paste Special to add the data as a new series, with series in columns, Y values in the first column, series names in the first row. The resulting chart is shown below right.

Series A and Series B, to be connected with arrows

Now copy the A-B Interwoven data (below left), select the chart, and again use Paste Special to add this data as a new series, with series in columns, Y values in the first column, series names in the first row, to produce the chart shown below right. (You may have to format the new series to show lines and no markers)

Series A and Series B connected with line segments

Now you need to format the line segments of the new series. Select the series and press Ctrl+1 (numeral one) to open the Format Data Series task pane or dialog. Click the End Arrow Type dropdown, and select the desired arrow type. The options include three arrowheads, a circle, a diamond, and a plain line.

Format Data Series Lines - End Arrow Type

Now click the End Arrow Size dropdown, and choose an appropriate size for the arrowhead. There are combinations of three arrowhead lengths and widths.

Format Data Series Lines - End Arrow Size

Now the line segments have been transformed into arrows.

Series A and Series B connected with arrows

Unlike the VBA approach of the old tutorial, the arrows are tied to the points in Series A and Series B. So if you change the axes in the chart …

Series A and Series B still connected by arrows

… or change the size or shape of the chart …

Series A and Series B still connected by arrows

… the arrows still match up to the points.

The VBA

You don’t need to use VBA to connect points with arrows, but it is a good sample application of VBA to your charts. Just so you don’t have to go back to the earlier post, here is the VBA Procedure that connects the points with line segments with arrowheads.

Sub ConnectTwoXYSeriesWithArrows()
  Dim myCht As Chart
  Dim mySrs1 As Series
  Dim mySrs2 As Series
  Dim nPts As Long, iPts As Long
  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 at least 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(xlCateogry).MinimumScale
  Xmax = myCht.Axes(xlCategory).MaximumScale
  Ymin = myCht.Axes(xlValue).MinimumScale
  Ymax = myCht.Axes(xlValue).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.ObjectThemeColor = msoThemeAccent3
        .ForeColor.Brightness = -0.25
        .Weight = 1.5
        .EndArrowheadLength = msoArrowheadLong
        .EndArrowheadWidth = msoArrowheadWidthMedium
        .EndArrowheadStyle = msoArrowheadTriangle
      End With
    End With

  Next

ExitSub:
 
End Sub

 

Peltier Tech Charts for Excel

Comments

  1. Peter Bartholomew says:

    Hi Jon
    Just another couple of ideas to throw into the mix. An alternative to leaving blank rows, which is awkward to achieve, is to allow the connector series to make a round trip from series A to series B and back. Series A lines will cover the surplus links from point to point. I used a couple of Named formulas
    pointNum: = CEILING( k, 3 ) / 3
    seriesNum: = 2 – ISEVEN( MOD( k-1, 3 ) )
    to gather the new data series using
    = CHOOSE( seriesNum, INDEX(A, pointNum, 0 ), INDEX( B, pointNum, 0 ) )
    No single-direction arrows though!

  2. Peter Bartholomew says:

    The following offers a fairly minimal volume VBA to insert ‘proper’ blanks into a dataset. It can either be event-driven or rely on the user clicking a button to refresh the data.
    Sub transcribe()
    If Evaluate(“Refresh.Needed”) Then
    Range(“y.bar”).Value = Range(“y”).Value
    End If
    End Sub
    where the test ‘Refresh.Needed’ refers to
    = OR( y y.bar )

  3. Peter –

    Re your first comment, unfortunately this approach is going to be as difficult to implement and maintain as a separate range containing the points for A and B mixed with blanks, and it is not going to allow one-ended arrows or different line colors for the connectors than for the series lines themselves.

    Re your second, maybe some characters didn’t come through properly, but I don’t understand what it is supposed to do.

  4. Peter Bartholomew says:

    Jon
    I apologise for leaving cryptic messages on your blog. I am always uncertain whether such contributions add value or merely distract the reader from the original post.

    As you say the first approach cannot give a single-ended arrow since the excursion from series A to B is a round trip. The link to the next pair is hidden by series A which can be chosen to match the plot background or contrast (any colour). There is complete freedom to choose series B line type. I am not relying on gaps in the link series so there are no major maintenance issues.

    I even produced a version that has no helper cells but performs the entire calculation using named array formulas in memory. By then though, VBA may be seen as the simpler option!

    In the second example all I am doing is using VBA to copy the y-series to a new range for plotting. The copy has true blank cells rather than formulas that give “”. I monitor the two ranges and present the user with a command button when a refresh is required (i.e. the two ranges have different content). The problem with my post was that the ‘not equals to’ seems to be treated as a blank XML tag.
    = AND( y = y.bar )
    would do as well.

Trackbacks

  1. […] an end arrow of the desired type and size. Coincidentally, I just published a new blog post, Connect Two XY Series with Arrows, which has a non-VBA approach to connecting plotted points with arrows, and updated code if you […]

  2. […] Idea to write a blog post about this technique became dusty in me OneNote, and probably would wait more if only Jon Peltier hadn’t attracted my attention to this topic again by his recent post. […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0