entering matching data for line charts

[johnston]

I'm sure this is naive. In Excel 2003 I'm trying to construct a line graph
in which there is is more than one set of matching x and y values. For
example, the data looks like this:
x1 y1 x2 y2
1/1/08 1 1/15/08 5
2/1/08 2 2/15/08 6
3/1/08 3 3/15/08 7
4/1/08 4

If I were drawing a scatter plot, I could enter matching x and y values, but
with a line graph I can plot the data only by rearranging it to look like
this.
x y1 y2
1/1/08 1
1/15/08 6
2/1/08 2
2/15/08 7
3/1/08 3
3/15/08 8
4/1/08 4

I'm sure there must be a simpler way, particularly if one has multiple data
sets. Any help would be appreciated.

Thanks,
johnston

 

[Jon Peltier]

Johnston -

There is a simpler rearrangement of the data:

x y1 y2
1/1/08 1
2/1/08 2
3/1/08 3
4/1/08 4
1/15/08 6
2/15/08 7
3/15/08 8


A line chart internally sorts the data by date in a line chart with a date
axis. The chart at first will not connect the points with lines, because by
sorting it introduces blanks within the ranges of Y values. Go to Tools menu

Options > Chart tab, and choose interpolate in the top section.

The other thing you could do is create the chart with the X1-Y1 data, add
the second series with the X2-Y2 data, convert the second series to an XY
series, and format the XY series to plot on the primary axis. The XY series
will plot nicely on the line chart's date axis.

Note, this XY-Line combination chart will not work in 2007. I've reported
the bug and can only hope it's fixed in Excel 14.

- Jon

 

[johnston]

Jon,
Your arrangement of the data is much simpler. Thank you. However, I didn't
quite understand your instructions. Do I create a line chart with the x1 y1
data and add the y2 data as a new series? (Excel doesn't allow me to enter
the x2 data.) Can you explain more fully what to do?

Thanks,
johnston

 

[Jon Peltier]

Create the line chart with the X1-Y1 data. Copy the X2-Y2 data, select the
chart, and paste special to add the data as a new series, with categories in
the first column. Change the series to an XY type (right click - chart
type - select a line type), and move it back to the primary axis. Note to
Excel 2007 users: the second series will lose its X values by creating a
line chart series, so when you convert it to an XY series you will have to
add the X2 data as its X values..

- Jon

 

[johnston]

Jon, thank you for the explanation. You saved me hours of aggravation.

johnston