7

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Leo...
  • 321
  • 2
  • 4
  • 14
  • Can you identify anything in a chart with 70 data series? – Fratyx Oct 22 '14 at 14:39
  • 1
    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... Oct 22 '14 at 14:52

2 Answers2

2

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

Fratyx
  • 5,717
  • 1
  • 12
  • 22
  • Thanks a lot for answering and trying to help. I don't know if I got it right, but if I did, then this means to me that I have to completely resort my data, from stacked (above each other) to horizontally distributed in columns where the first column is the time points and then each consecutive column contains data of one group. If my understanding is correct, then this will be more time consuming than the manually selecting thing that I am currently doing as the time points of these data are not similar, and the data will come in the future again as stacked data that needs a new sorting! – Leo... Oct 22 '14 at 15:26
  • I know that there are other programs that deals better with stacked data, like R, but I would really appreciate if this can be done in excel, which is theoretically at least, must be possible – Leo... Oct 22 '14 at 15:26
  • @Leo... It took me about 20 secs. Is that too long? The result is exactly as in your description. (see the updated post) – Fratyx Oct 22 '14 at 16:40
  • Unclear what is going on here (and the German headings aren't making it better). Assuming what you have is correct, you should list the steps more precisely, and maybe show the chart that you ended up getting (since that was the question). Took me a few minutes to get the desired result with these instructions (not 20 seconds). – fgblomqvist May 27 '18 at 01:33
1

A way to do this in Excel 365 is:

  1. Select the data
  2. Go to Data -> From Table/Range to open the Power Query editor
  3. Select the columns with grouped data
  4. Select Transform -> Pivot Column
  5. Select the column with the values corresponding to the grouped data
  6. Under Advanced Options change the value aggregation to Don't aggregate
  7. Click OK, then Home -> Close and Load

This should give you the data formatted in such a way that you can select it and create a chart as normal.

The Bic Pen
  • 773
  • 6
  • 21