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

5
votes

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.

1
excelgraphplotscatter-plot
Can you identify anything in a chart with 70 data series? – Fratyx
yes it is possible, depending on the sort of information you are looking for in the graph, and especially if there is an additional index that creates another grouping, like in my case. But for the ease of asking and answering I just put one grouping index as the same principle goes for both, and knowing one will answer the second. Anyway, if this is the only thing that bothers you in my question just consider the index is 2, 10, or 15. just name it :p – Leo...

1 Answers

2
votes

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)

enter image description here