Data Techniques

A visitor to my web site asks, “How can I connect corresponding points of two different XY series in a chart?”

In other words, how can this chart

be converted into into this chart

This is pretty easy by adding a third XY series that includes all of the data for the original two series, properly sequenced to connect the points.

There are two blocks of data below. The block on the left has the data for A (shaded blue) stacked on the data for B (shaded orange). A column of numbers is inserted to the left, counting the points of each series from 1 to 8. The block on the right has been sorted by these inserted counting numbers, producing alternating data from series A and B. When plotted on the chart with A and B, the new series connects the points.

The problem is that not only are the corresponding points of series A and B connected (solid lines below), but also each point of series B is connected to the next point of series A (dashed lines below).

How can we eliminate those dashed lines? By default, an Excel chart draws a gap for a blank cell, so we need to insert blank rows in our data.

We adjust our methodology to include a region of blank cells below the data for series A and B, below left. When sorted, we now have our data sequenced as Series A – Series B – Blank. When this data is added to our chart of A and B, the lines connect our points perfectly. (In fact, the last row, the blanks next to the index 8, is not needed.)

While working on this data arrangement, I realized it was what I needed to enhance a step chart (Line Chart vs. Step Chart) to omit the “risers” and draw only the horizontal segments (Step Chart Without Risers). I guess there are more reused solutions than new solutions.

You can download a simple workbook that shows these charts from the link below:

Connect XY Series.xlsx