Create line chart for many series

Question

I have data in my sheet like the following:

Day         Area    AVG(Time)
-----------------------------
2013-07-01  LINE1   49.703432
2013-07-02  LINE1   38.119913
2013-07-03  LINE1   30.6651
2013-07-04  LINE1   61.163277
2013-07-05  LINE1   29.757895
2013-07-01  LINE2   214.900325
2013-07-02  LINE2   185.053037
2013-07-03  LINE2   81.827013
2013-07-04  LINE2   72.822807
2013-07-05  LINE2   124.351707
[...]

LINEs are up to 20, days is up to 60. I would like to create a chart in which the X-axis will be days, the Y-axis numbers and for each LINE Excel will draw a single line with points. I tried a few types of charts but still have nothing presentable.

pnuts pnuts · Accepted Answer · 2013-08-21T11:27:41

Select ColumnsA:C, Insert > Tables - PivotTable, Pivot Chart and choose New Worksheet or Existing Worksheet to suit. Drag Day to Axis Fields (Categories), Area to Legend Fields (Series) and AVG(Time) to Σ Values. If required, left click on the contents of Σ Values and select Value Field Settings... and Sum.

Right click the chart (presumably a pie) and select chart of choice via Change Chart Type...

SO18355462 example

To remove references to (blank) restrict you input to the PT by clicking on the PT and PivotTable Tools > Options > Data, Change Data Source.

Create line chart for many series

1 Answers