Problems Creating Excel Charts With Multiple Series

**adiyesaya** · 04-11-2012, 02:49 PM

Hi,

I currently have this data set as an example:

12-3-2011 200 29271
12-3-2011 201 29601
12-3-2011 202 1206149
12-3-2011 203 16149

this data goes on until today's date (1st column), the second column repeats from 200 until 203
the question is, how can I create an excel Chart where 12-3-2011 is in the vertical axis and the 3rd column is the horizontal axis and the 2nd column are displayed as different lines (each one a different color).
until now I only succeed in creating 1 line. I don't know how to add 201 until 203 into the chart.

Thanks for your help,
Adi

**Jon_Peltier** · 04-11-2012, 09:47 PM

Add headers:

Please Login or Register  to view this content.

Select the data, and create a pivot table. Put Series in the columns area, Date in the rows area, and Horizontal in the data area. This is the data arrangement that makes it easy to make a chart with data along the horizontal and the third column of the raw data as the vertical, which is the conventional way to plot times and dates.

What I usually do here is copy the pivot table, go to a blank area of the worksheet, and use Paste Special-Values, so the data is no longer in a pivot table, which provides more flexibility in the chart.

You can make a line chart of this data, which will have dates along the bottom, and one line per series number.

To get the chart to go the other (unorthodox) way, you need to make an XY chart instead of a line chart. Then you'll have to edit the series formulas for the chart series.

Select one of the lines, and look in the Formula Bar. You'll see something like this:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$20,Sheet1!$B$2:$B$20,1)

This can be thought of as

=SERIES(<series name>,<x values>,<y values>,<plot order>)

Rearrange the series so the old x and y values are switched to become the new y and x values. Repeat for each series.