Excel – How to create a chart in Excel with dynamic series based on the data content

chartsmicrosoft excel

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):

  1. Convert your data into a Table. Select any cell in your data set and select Insert > Table.
  2. Create a Pivot Table. With your new Table still selected, select Insert > PivotTable.

  3. Setup your Pivot Table. For your example, set your Pivot Table as follows:

    • Row Labels: Date
    • Column Labels: Series
    • Values: Values

  4. Create a Pivot Chart. With your Pivot Table selected, go to Insert > Line with Markers (Chart). The format to your preference.

Chart Example

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.

Related Question