4

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.

Improve this question

2 Answers 2

Reset to default
15

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.

Improve this answer
3
  • 1
    @GregH did this help at all? If not, please let us know so we can get you a working answer. Thanks
    – dav
    Commented Feb 28, 2013 at 13:33
  • This works, but you do end up with gaps in your line series. Is there a way to address this?
    – Ari Cooper-Davis
    Commented Apr 21, 2023 at 13:28
  • @AriCooper-Davis I have the latest version of Excel .... Right-click the chart, click "Select Data" from the popup menu, click the "Hidden and Empty Cells" button, choose "Connect data points with line"
    – Mark Plumpton
    Commented Sep 21, 2023 at 21:58
0

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.

Improve this answer
2
  • 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
    Commented Dec 6, 2012 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
    Commented Dec 6, 2012 at 17:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .