Excel creating multiple scatter plots from one table

Question

I have a table that looks something like this...

Date      Name   Amount
July 1    Joe    30
July 2    Kat    25
July 2    Joe    35
July 3    Ray    45
July 4    Joe    50
July 4    Kat    20
July 5    Kat    40

Now what I want to do is create a scatter plot for each person to track what they've spent over time. If i transform this into a table, insert a chart and use a chart slicer, it's very easy to visualize ONE person's progress:

But if I select two people, it MERGES their numbers together into one scatter plot:

I want these to both appear as separate series on the same plot.

While it would be easy to just re-arrange this information where every person has their own column, the problem is I'm not just trying to plot information for three people. It will be hundreds. and new people may constantly be added. So my log sheet is going to be a very long "running list" of everyone's names and expenses. I can't possibly add all as separate series criteria in chart data. If I were to make columns for every person the information would stretch very far.

Answer 1

If you arrange your pivot table as shown below, with dates in the row area and names in the columns area, the pivot chart will show distinct lines for each name. The lines don't go across the gaps in the table (first chart), so right click on the chart, choose Select Data, , click on the hidden and empty cells button at the bottom left of the dialog, and depending on your versions of Excel choose Interpolate or Connect Data Points with Line (bottom chart).