scatter with movement tracking

Steph77

Board Regular
Joined
Sep 18, 2014
Messages
119
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!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
open

Scatter-with-movement-tracking-June-24-2019.jpg


Is this the type of graph you were looking to create?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top