Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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.

share|improve this answer
    
@GregH did this help at all? If not, please let us know so we can get you a working answer. Thanks –  dav Feb 28 '13 at 13:33

In the Charts ribbon, click the Switch Plot button under Data. This will cause your line chart to display your data as columns instead of rows.

The line chart defaults to column display. You could also switch your spreadsheet around so that the data runs down the page instead of across.

share|improve this answer
    
This doesn't work. It seems to basically concatenate the "Date" and "Series A" into a single value on the X axis and when I click the "switch plot" button, it those seem to switch to a series. What I want is the date on the X Axis and there to be a line on the graph for each color. –  GregH Dec 6 '12 at 16:58
    
Yes. I've been using Excel (and Lotus 1-2-3 before that) for decades now. This does work. Something didn't work right, but basically, you just need to switch the chart to use columns and not the default rows. I even copied some of your example data and did a test to prove my method. –  Ian Atkin Dec 6 '12 at 17:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.