How to plot multiple grouped data in one excel scatter plot with lines

Question

I am facing some difficulties with plotting grouped data (by index) in one graph (scatter plot with lines) in Excel, and I will appreciate a lot your help.

My data are in three columns: The first column is the index of the data or the group (i.e. a unique number for every set of data) the second column is the time and the third column is the data

Group, Time, Data
1   1   12
1   3   12
1   4   28
1   8   56
1   12  37
1   24  40
1   48  34
2   0   7
2   1   14
2   4   6
2   8   63
2   12  4
2   24  35
2   48  3

und so on.

and I want to plot the data vs. time for each index i.e. data group alone, but on the same graph.

Until now, I was always doing it manually by adding each data set separately to the graph. But I think there should be a more clever and easier way to do it, especially that sometimes I have a lot of data (index number can reach 70 or 80).

Thanks a lot in advance.

Answer 1

You can create a pivot table on all your data. Use 'Group' as column headers and 'Time' as row headers. The resulting pivot table will have all time points from all groups as rows and your groups as columns. Each columns of course has entries only at these time points which are included in its group. The other cells are empty. If you just select the data range of this pivot table without column headers, you can get charts from the data as a plot chart omits empty cells.

Update

That is the result pivot table of your test data. The sorted data are in the red frame. (Forget the total results)