Connect Two XY Series
by Jon Peltier
Sunday, May 25th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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. The data has to be properly sequenced, with a point from series A, then a point from series B, then a blank row, then the next point from series A, etc.
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.
To arrange the data, start with the sequence below. The left hand block shows the original data. The middle block shows the original data copied and pasted into a new range. A column has been inserted to the left of the data, showing the index of each point (1, 2, 3, etc.). Notice that a set of blank rows below the data has also been given index numbers. If there are N points, only N-1 blank rows are needed. In the third block, the data has been sorted by these indexes, leading to the alternating A-B-blank sequence.
|
|
|
To create the chart, select the data for series A, and create an XY chart, using the markers without lines option. Copy the data for series B, select the chart, and use Paste Special to add the data as a new series. Finally copy the sequenced A-B data from the right hand block above, select the chart, and use Paste Special to add the data as a new series. Change this XY series type to the lines without markers option, or simply format the series to have lines but no markers.
Related Posts:
- Fake Line Chart (Dummy XY Series for X Axis)
- Connect Two XY Series with Arrows (VBA)
- Step Chart Without Risers
- Order of Points in XY and Line Charts
- Line Charts vs. XY Charts
- Excel Category Axis Types
- Line Chart vs. Step Chart
- Sample Parallel Coordinate Chart
- Connect Two XY Series with Arrows – 2007 Error
- VBA Conditional Formatting of Charts by Series Name
Posted: Sunday, May 25th, 2008 under Data Techniques.
Comments: 6
Comments
Comment from Eyad Husseini
Time: Sunday, May 25, 2008, 6:39 pm
Mr. Peltier,
I am one of your fans who has learned many and many from your great web site. I have read Excel 2007 Chart by John Walkenbach where i saw you name first time. Since that time, I have 3 meals daily in addition to my Excel Charting Meal in Jon Peltier Resturant. I implement your tricks and tips in my daily task as a Planning and PMCE – Project Monitoring and Contorlling Engineer. Thanks Thanks and Thanks a lot Mr. Peltier and I am waiting for an Excel Charting Book from you.
Regards,
Eyad Husseini
PMCE
Comment from Jay
Time: Monday, May 26, 2008, 12:25 am
Jon
Thanks for this wonderful tips on connecting XY series.
While on this, I think it will be improved data visualization if I can show an arrow (from earlier to new levels) instead of simple connecting-lines.
Could you please advice us how this can be done?
Thanks
Jay
Comment from Jon Peltier
Time: Monday, May 26, 2008, 8:34 am
Jay -
In versions of Excel up to 2003, this can be achieved only through the addition of autoshapes, that is, lines with arrowheads drawn on the chart. These are tricky to align with the points, and if any little thing happens in the chart, the lines will have to be realigned.
I’ve also seen techniques where several extra points are added to each section of the series that connects each pair of points, such that these extra points define the shape of an arrowhead. This is tricky to define the points, given unequal X and Y scales, such that the arrow heads are nicely proportions, and if anything in the chart changes, these extra points have to be redefined.
I’ve criticized the eye-candy formatting added to Excel 2007 charts to the detriment of chart functionality, but some of the added formatting is actually useful. You can format the lines in a chart series as if they are autoshapes, and by adding arrowheads to the lines, you can achieve the effect you have requested.
Comment from Jay
Time: Monday, May 26, 2008, 9:22 am
Jon
Many thanks for this clarification. Yes, I agree that this arrow will be helpful. But, if it comes with the baggage of autoshapes, It may be difficult to maintain and therefore not advisable. I shall explore this in my charts and check. Thank you for your clarification. You are doing a great service to us excel community out there. May God bless you.
Jay
Comment from Jon Peltier
Time: Monday, May 26, 2008, 10:54 am
Jay – I’ve written a bit of VBA that will draw and redraw the arrows:
Comment from Jiby
Time: Wednesday, March 31, 2010, 11:19 am
Thank you Jon. Once again you are GREAT.
I have been looking for VERY long how to show movements in my bubble charts to indicate where a bubble was in PERIOD-1 and where it stands NOW.
When will Sweden/Norway create a Nobel prize for Excel Science ? You deserve it.
Greetings from Belgium
-Jiby-
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.