plotting simple XY axis graph

[Timboi]

This is probably an easy question but I've been trying to plot 2
columns of numbers as a single line graph in Excel 2000, but this does
not seem to be possible using the line graph type of chart (it plots
each column as it's own series rather than the relationship betweeen
the 2 series) - it only seems possible by using the scatter type - is
this correct?

cheers,
Timboi

 

[Tushar Mehta]

XL tries to be overly helpful and makes a bad assumption about how to
plot data in a line graph. One way out is to ensure that the x-values
have no column heading and the y-values do. Another is to start the
chart wizard,then in step 2, select the Series tab. In there make the
necessary adjustments. Copy the contents from the the Value field for
Series 1 to the Category field, then delete series 1 with the Remove
button.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Disgruntaled student]

this is more of a hwork then it's worth i'm using a piece of paper and a pencil

 

[Jon Peltier]

What confuses a lot of new users, I think, is the icons in the Chart
Type dialog (step 1 of the Chart Wizard). The Line chart is shown as a
line connecting points, while the XY Scatter chart is shown with just
points. Either chart type can be formatted with or without markers at
the points, and with or without lines connecting the points.

One true difference between Line and XY Scatter is in Excel's handling
of the category data as wither discrete non-numeric category labels
(Line chart) or as variable numeric data (XY Scatter chart). Another
real difference is Excel's interpretation of the first column (if you're
plotting by columns): in a Line chart, if it's numeric, Excel will treat
the first column as yet another plottable series, not as related to the
category axis; if it's non-numeric or a date, Excel will use it for the
category axis. Excel always assumes the first column in an XY chart's
data range is the X data. You can override Excel's treatment of the
first column in a Line chart by placing labels at the top of each column
of Y values, and leaving the cell above the X values blank: select the
entire range including labels and blank cell, and the blank tells Excel
to use the first column for categories, and the first row for series
names. In fact, if you leave a multiple-cell range blank in the top
left, Excel uses multiple columns for the categories and multiple rows
for the series names.

- Jon

 

[Timboi]

Reply under reply

What confuses a lot of new users, I think, is the icons in the Chart
Type dialog (step 1 of the Chart Wizard). The Line chart is shown as a
line connecting points, while the XY Scatter chart is shown with just
points. Either chart type can be formatted with or without markers at
the points, and with or without lines connecting the points.

One true difference between Line and XY Scatter is in Excel's handling
of the category data as wither discrete non-numeric category labels
(Line chart) or as variable numeric data (XY Scatter chart). Another
real difference is Excel's interpretation of the first column (if you're
plotting by columns): in a Line chart, if it's numeric, Excel will treat
the first column as yet another plottable series, not as related to the
category axis; if it's non-numeric or a date, Excel will use it for the
category axis. Excel always assumes the first column in an XY chart's
data range is the X data. You can override Excel's treatment of the
first column in a Line chart by placing labels at the top of each column
of Y values, and leaving the cell above the X values blank: select the
entire range including labels and blank cell, and the blank tells Excel
to use the first column for categories, and the first row for series
names. In fact, if you leave a multiple-cell range blank in the top
left, Excel uses multiple columns for the categories and multiple rows
for the series names.

- Jon

Thanks Tusha and Jon - is there a good book you can recommend that
makes sense of all this stuff? It's probably quite easy once you 've
played around with it for a while but it looks like there might be a
few pitfalls along the way, so I'm sure somone has written some nice
stuff on it.

Cheers,

Timboi

 

[Jon Peltier]

I doubt there's a single decent source with all of this stuff. As you
say, it's kind of hunt and peck until you figure out things. I learned
most of what I know on the subject through (A) playing and trying to get
things to work, and (B) going to many web sites in search of help.

I've written a FAQ on Excel Charting:
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209
and I have an extensive web site devoted mostly to the topic, including
a half-completed tutorial on charting:
http://www.geocities.com/jonpeltier/Excel/Charts/index.html

Some other decent sites (lifted from the links at the end of the FAQ):

http://tushar-mehta.com
Tushar Mehta's web site, which includes charting content and much more.

http://j-walk.com
Web site of John Walkenbach, author of myriad Excel books, including the
only book available on Excel Charts; also the author of Chart Tools, a
very useful add-in.

http://appspro.com
Site of Excel MVP Rob Bovey, where you'll find the indispensable Chart
Labeler add-in.

http://bmsltd.co.uk/Excel/Default.htm
Stephen Bullen's web site, which features a section on advanced charting
techniques.

http://andypope.info/charts.htm
Web site of Andy Pope, who has developed some creative charting solutions.

http://edferrero.m6.net/Charting.html
Ed Ferrero's charting examples.

http://www.prodomosua.it/ppage02.html
Fernando Cinquegrani's web site, containing many downloadable utilities
(it's in Italian, but worth it for the clever techniques he's implemented).

- Jon