entering matching data for line charts

J

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
 
J

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
 
J

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top