Excel: Plotting multiple series in a scatter plot

Question

I have several different students and I'd like to plot their test scores changing over time. There are multiple students but only a few tests each.

Name    Date    Score
Afaf    10/1/2014   2
Afaf    4/15/2015   3
Ahmed   10/8/2015   1
Ahmed   2/16/2016   1
Ahmed   3/5/2016    1
Arie    11/3/2015  -1
Arie    3/23/2016   1

So far I've found that the scatter graph gives me what I want:

However, I have to manually select each individual student when I'm adding new series to the graph. I can't get Excel to recognize the 'Name' column as categorical (i.e. each name is its own series):

This is really slow because there's over a hundred students in the dataset. Is there a quicker/better way to do this? I could probably do it in R, but this is for a person who doesn't know R and needs to be able to replicate this in the future.

Thanks in advance for any help.

Answer 1

Because you do not have different students taking the tests on the same dates, every student will require their own X series in addition to their own Y series. As such I would recommend on a separate worksheet or off on some part of the current spreadsheet you are not using you develop a list series for platting.

Step 1) would be to generate a list of unique names. Hopefully you do not have two John Smiths in your student list. If you so you will need to figure out a way to make them unique. I am going to suggest you put all your student names in a horizontal row. There are formulas that can do this for you. There is also a built in excel command that will generate a list removing duplicates. (if that works only in columns not worries, you can just copy and past transpose to get the list horizontally.

Step 2) would be putting a blank column between each name. This is required since each student has their own unique list of dates for tests.

Step 3) would be to build a list of test dates under the persons name, and their corresponding grade in the blank column. Again there are some formulas for this such as LOOKUP, VLOOKUP, and INDEX MATCH combination. You may even want to consider the AGGREGATE command if the dates are not chronological. otherwise connecting the dots can get messy. you the AGGREGATE LARGE or SMALL to Generate a sorted list of dates belonging to the students name.

Step 4) Add each series to you chart. According the MS Specification you can apparently have a maximum of 255 series.

IF You have your data layed out as suggested, record a macro of you making the first graph with the first series. Stop the macro and then go look at the code. fi you can see what was done record the same process this time add a second series after the first. you should notice small changes. Repeat this pattern until all students have been covered.

You should be able to search for the concepts talked about to find answers on SO that can be edited to suit your needs.