Excel - XY charts: can't plot Y vs. value of X

Asked By Mike on 26-Nov-08 04:53 PM
I'm trying to plot the values of Y (in column C) vs. the values of X (in
column A), but the chart plots the spreadsheet line number rather than the
column A value, even though the pop-up window shows column A is select for
the x-axis.

Also, it is now very difficult to select the data points in different
columns. It was easy before, by holding down the shift/control keys and using
the arrow keys.

I really hate the 2007 version!  I was VERY proficient with XL before, and
now I am totally helpless trying to create some simple X-Y charts. The help
tools are totally worthless!  Microsoft really screwed up a good product.

ShaneDevenshir replied on 26-Nov-08 09:13 PM

What's in column A and C?  And are you plotting column B also?

When I enter numbers in column A and C and then select the range A1:A12,
C1:C12 and choose Insert, Scatter, and pick Scatter with straight lines I get
exactly what I would expect.

Mike replied on 26-Nov-08 09:27 PM
Yes, it's easy with simple arrays. But, I've got thousands of rows of data
and entering the range manually is not that easy.  I experimented and found
solutions, but the new method is not as simple as the old program, although
it does offer some new features.

I found that if the data column label is included in the range than it
screws up the XY plot.  I changed the range to only contain numbers and it
worked okay.  The old system asked you if the first entry was a label or a
value; very handy.

ShaneDevenshir replied on 27-Nov-08 02:38 AM

I still can't duplicate your problem - if I have titles (labels) for the two
ranges, columns A and C:

1. Click cell A1 - the title of the first set of data
2. Press Shift+Ctrl+Down Arrow (the whole column is selected down through
all the solid data.
3. Keep the Ctrl key down and click C1
4. Press Shift+Ctrl+Down Arrow (the whole of column C is selected and A is
still selected.
5. Choose Insert, Scatter, give me the correct data on both axis and it uses
the tilte in column C as the Legend.

Regarding a few new features - I have personally documented 1800+ new
features in 2007.  When Excel went from version 2002 to 2003 I was hard
pressed to find more than 10!

I personally agree with you regarding handling of the data series in the
dialog boxes for charting, but hopefully they will clear these issues up in
forthcoming versions.

If this helps, please click the Yes button

Shane Devenshire
Leigh Thorpe replied on 18-Mar-09 05:30 PM
I also had a problem with Excel plotting my data against the line number rather than the values I was trying to use for x, and I noticed that some of the entries in my data were numbers stored as text, rather than proper numbers.  Fixing that resolved the problem.

Is there a quick way to ensure all entries in a large array are in fact numbers?  I had to change mine one at a time.