Graph with X and Y lines both plotted in numerical order

[solidbronze]

Hello, I hope someone can help!

I'm trying to create a chart which allows several points with XY co-ordinates to be plotted, and then for lines to be drawn joining each X number in numerical order and each Y number in numerical order. Attached is a sample .xlsx file.

Essentially, what I'm trying to achieve is in the Photoshopped image below: the red line joins the X co-ordinates in the order 1,2,3, and the green line joins the Y co-ordinates in the order 1,2,3.

Excel Chart XY.png

Thanks for your help!

Andrew

[MrShorty]

Excel scatter charts connect the points in the order they appear in the spreadsheet range. In order to connect the points in "X" order, the data in the spreadsheet must be sorted on the X column. In order to connect the points in Y order, the data in the spreadsheet must be sorted on the Y column. In order to get both, you need two copies of the data (and two data series in the chart) -- one sorted on X and another sorted on Y. In your sample spreadsheet, your data are sorted on Event. Here's what I did:

1) I don't know if the existing copy sorted on Event is important to your spreadsheet, so I made two copies of the data (for a total of three copies).
2) Copy 1 is already sorted by Event, so I left it alone.
3) I select copy 2 and sort that copy of the data table on X.
4) I select copy 3 and sort that copy of the data table on Y.
5) I select the chart -> Bring up the source data dialog -> Add two new data series to go with the two new copies of the data table.
6) Format each of the new data series to be "line without marker".

That should be very close to your picture. Will that work for you, or is there more the question?

[solidbronze]

Brilliant! Thanks, Mr Shorty - that's exactly it. I hadn't thought of duplicating the data. You've saved my lots of time!

Thanks again - Solved.