scatter with movement tracking

[Steph77]

Hi all

I'd like to build a scatter graph with a slight difference. In the source data there are 2 variables for each person, and both can shift in either direction (up or down). Normally we plot a single point for each person on a scatter and just have different scatters for different data drops, but I've been asked if it's possible to plot all the data drops (up to 4 per year) on the same graph, with lines connecting the same person as they "move" around on the scatter between drops.

I feel like this isnt possible with Excel (without complex VBA) from what I know, but I'm not super-familiar with graphing data in Excel, normally I produce dashboards heavy on numerical analysis with the output as KPI comparisons with the odd simple pie or bar chart. One of my initial thoughts was to build a sparkline for each person, but the fact it may sometimes have to go "backwards" on the X axis is stumping me on that approach.

Really just open to suggestions on how I might be able to accommodate this request without spending ages building something really technical and cumbersome if possible.

Thanks in advance!

 

[Dave Paradi]

I believe you can create the chart you want using the Scatter with Straight Lines chart type. The key is in defining each data series. Selecting the data and creating the chart is unlikely to work thr first time since Excel won't figure out which are the X values and Y values for each person. You can use the Select Data dialog box to define the series name to be the person's name, the X range to be the X values for that person and the Y range to be the Y values for the person.

Here is an example of some data, how one of the series is defined, and the resulting graph.

Is this the type of graph you were looking to create?

 

[Steph77]

Yes that's it (I'd want data drops across the top and people down the left but I'm sure I can make that work too) - I was totally unaware this was even a chart option in Excel - learn something new every day

Thanks