XY Charts Archives - Page 6 of 6

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.

Connect XY Series with Arrows

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. [Read more…] about Connect Two XY Series with Arrows (VBA)

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