Problem with xy scatter with dates

[janet]

I am trying to create an x-y scatter chart where both the x values and the y
values are dates. I highlight the 2 adjacent columns of date data and click
on Insert>Scatter. Instead of a scatter graph, I get a line chart with 2
lines (the two data columns). The x values on the graph are simply a count
of the data points as in a line chart.
If the 2 columns of data are not dates, I get the correct scatter chart, but
not if the x-values are dates in which case I get a line chart. How can I
get a scatter chart with both x-values and y-values as dates?

 

[Jon Peltier]

You could select just the Y values, start the wizard, and in step 2, click
on the Series tab, then select the X value dates.

Or you could put a label over the Y value dates, and leave the cell above
the X values blank, select the row with one label as part of the data range,
and start the wizard.

- Jon

 

[Del Cotter]

I am trying to create an x-y scatter chart where both the x values and the y
values are dates. I highlight the 2 adjacent columns of date data and click
on Insert>Scatter. Instead of a scatter graph, I get a line chart with 2
lines (the two data columns). The x values on the graph are simply a count
of the data points as in a line chart.

It's not a line chart, as you can see by going to the x axis Format
Scale dialogue. You'll see the scale is an interval scale instead of the
characteristic Line Chart category scale.

What you've done is labelled both columns, so the wizard thinks you're
inserting two y series and no x. Move the left column heading
temporarily out of that cell and run the wizard again with the cell
blank. The wizard will properly pick up the x scale series then, and you
can move the column header back.

 

[Jon Peltier]

It's not a line chart, as you can see by going to the x axis Format Scale
dialogue. You'll see the scale is an interval scale instead of the
characteristic Line Chart category scale.

What you've done is labelled both columns, so the wizard thinks you're
inserting two y series and no x. Move the left column heading temporarily
out of that cell and run the wizard again with the cell blank. The wizard
will properly pick up the x scale series then, and you can move the column
header back.

Normally when you create an XY chart, Excel uses the first column as X
values, regardless of the presence or absence of any labels. This is in
contrast to the behavior when creating a Line chart. I think the date
formatting of the data interferes with Excel's range parsing algorithms, so
the XY chart in this one case behaves like a line chart.

- Jon

 

[janet]

I am in Excel 2007, and I don't see a series tab. I tried using the Select
Data window but excel insists on creating a line graph when I change the data
to the 2 columns of date values.

 

[Jon Peltier]

I hate 2007. You should be able to click the Edit button under Horizontal
Category X Axis Labels to select the range you want, but I guess they didn't
think you'd want to change from generic numbers (1, 2, 3,...) to real data.
So the button is disabled.

So follow the other approach, which Del also mentioned, and put a label
above the column of Y values and remove any label from above the column of X
values, then make the chart.

Well, there's a third way. After making the chart with just Y values, the
series formula looks like this:

=SERIES(,,Sheet1!$C$4:$C$26,1)

Click in the formula so the cursor is blinking between the first and second
commas, then select the range with the X values.

- Jon

 

[janet]

Eliminating the column title on the X-values did the trick. Thank you so
much.

 

[Jon Peltier]

My bad. Well, not just mine, the dialog design led me to this incorrect
conclusion.

XY charts don't have "category labels", so that half of the dialog is to be
ignored for an XY chart. In 2003, the category labels input box was hidden
for XY charts, but that interface was nicely designed.

So. In the Select Data dialog, select the series, then click the Edit button
above the series list. On the child dialog that appears, you can enter the X
value range.


- Jon