How would I define a chart in Excel (2010) that shows a time series graph containing various series based on the data content itself? For example, here is a data set:
Date Series A Values
11/1/2012 Blue 100
11/1/2012 Red 200
11/1/2012 Green 500
11/1/2012 Purple 1000
11/2/2012 Blue 150
11/2/2012 Green 400
11/2/2012 Purple 500
11/2/2012 Orange 900
11/3/2012 Blue 40
11/3/2012 Red 100
11/3/2012 Orange 400
What I want is a time series chart with one series for each color. Sometimes the color appears on the given date and sometimes it does not. It is basically dynamic. What would show on the chart are five lines (one for Blue, Red, Green, Purple, Orange) with the associated values. How would I do this? I can get them all to show up as a single series but can't quite seem to figure out how to break the series out across the entire timeline on a single chart.
Best Answer
Here's another approach (assuming Excel 2010):
Insert > Table
.Insert > PivotTable
.Setup your Pivot Table. For your example, set your Pivot Table as follows:
Create a Pivot Chart. With your Pivot Table selected, go to
Insert > Line with Markers
(Chart). The format to your preference.The major advantage of using this setup is that when you update info in your Data Table and refresh your Pivot Table, your Pivot Chart will automatically be updated with any new dates, series or values.