Excel – Splitting data into multiple series’ in Excel Graph


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?

Best Answer

  • 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

  • Related Question