Splitting data into multiple series' in Excel Graph

Question

I have data from a csv formatted as such:

StartTime  EndTime  Duration  Type
---------  -------  --------  ----
43:04.7    43:06.9  2200      long
43:06.9    43:08.7  1825      middle
43:08.7    43:09.6  905       short
43:09.6    43:11.9  2231      long
43:11.9    43:13.7  1794      middle
43:13.7    43:14.5  796       short

There are several thousand more rows.

I want a line graph with StartTime as the X-axis, duration as the Y-axis, and 3 separate lines: one each for long, middle, and short.

Can Excel do this with this dataset or do I have to reformat it? What is the best way of making this graph?

Answer 1

You can add three formulas next to your data, and plot these columns

Assuming data is in columns A:D, starting in row 3

add labels to cells E1, F1, G1 = long, middle, short

add formulas to cells

E3:  =IF($D:$D=E$1,$C:$C,NA())

F3:  =IF($D:$D=F$1,$C:$C,NA())

G3:  =IF($D:$D=G$1,$C:$C,NA())

copy down for as may rows as you have, and plot columns E, F, G

Answer 2

The fastest way would be to sort your data by Type so you have all the long's, middle's and short's together and then add your 3 lines.