XY scatter chart - data points don't align properly on X axis

[wtokyo]

I tried to create a simple XY scatter chart where the vertical axis
represented the cost per gallon of gas and the horizontal axis showed each
month for 2008. (Although I thought this type of chart would be considered a
line chart, Excel considered this to be a scatter chart.)

Though I wanted each month to be displayed, beginning with January and
ending with December, the X axis on the graph reads:
Nov-07, Jan-08, Feb-08, Apr-08, Jun-08, Jul-08, Sep-08, Oct-08, Dec-08.
March, May, August, and October are omitted. Although all 12 data points
appear on the graph, none of the data points are aligned with the proper
month.

Additionally, when I tried to display a chart showing monthly prices going
back 20 years, all the data points were displayed in one big clump, rather
than being spaced out over the 20 years. I had expected to see 240 data
points.

I would greatly appreciate any help in solving these 2 problems.
wtokyo

 

[Carl Witthoft]

I tried to create a simple XY scatter chart where the vertical axis
represented the cost per gallon of gas and the horizontal axis showed each
month for 2008. (Although I thought this type of chart would be considered a
line chart, Excel considered this to be a scatter chart.)

Though I wanted each month to be displayed, beginning with January and
ending with December, the X axis on the graph reads:
Nov-07, Jan-08, Feb-08, Apr-08, Jun-08, Jul-08, Sep-08, Oct-08, Dec-08.
March, May, August, and October are omitted. Although all 12 data points
appear on the graph, none of the data points are aligned with the proper
month.

Additionally, when I tried to display a chart showing monthly prices going
back 20 years, all the data points were displayed in one big clump, rather
than being spaced out over the 20 years. I had expected to see 240 data
points.

I would greatly appreciate any help in solving these 2 problems.
wtokyo

If your axis font is big and your chart is small, it will only print
every other or every third label. And, unless you take a careful look
at the major tick marks, AND at the day-of-month in each cell, Excel may
well plot your data off what you expect to be the first, or middle, of
each month.

Can you please post the exact formula for the plotted points? I.e. click
on a point and copy the stuff in the formula bar that looks something
like
=($a$1,$a$1:$a$10,$b$1:$b$10)
and give us a sample of the data. By the way, are you certain the dates
are entered as numbers, not text strings?

And, yeah, it's confusing but Excel's idea of a "line chart" is just a
line whose x-axis values are a sequence of names called Categories. The
"scatterplot" is the only plot type that plots numeric values on both
axes.

 

[wtokyo]

I clicked on a data point and the following appeared in the formula bar:
=SERIES(Sheet1!$B$2,Sheet1!$A$3:$A$14,Sheet1!$B$3:$B$14,1)

Cell A2 is text - Time
Cell B2 is text - Cost per gallon

All the data in column A are in Date format. For example, Cell A3 is
"Jan-08" (Cell A3 is the first data point), cell A14 is "Dec-08". The data
in column B are in general number format. The last data point is in cell B14.

wtokyo

 

[Carl Witthoft]

OK, I set up a little model w/ the following data:

January 1, 2008 1
February 1, 2008 2
March 1, 2008 3
April 15, 2008 4
May 1, 2008 5
August 1, 2008 3
September 1, 2008 7
October 1, 2008 2
November 10, 2008 4
December 1, 2008 5

Now, if I format the cells to look like "Month-08" , then that is what
you'll see for X-axis labels. BUT: the labels are misleading. Excel
automatically selects the max and min range values and then selects
certain subdivisions for the major tick marks. For the above data, even
tho' the graph displayed "Jan-08" ,"Feb-08" etc. , when I changed the
format to month-day-year as above, the "true" tickmark locations were
revealed to be Jan 15, Mar 5, Apr 24, Jun 13, and so on.

So I suspect you need to manually set the max and min range as well as
the major tickmark division if you want to see the tickmarks on the
first of each month.

HTH
Carl

 

[wtokyo]

Thanks. I understand your answer, but could you explain how to set the
min/max values?

 

[Carl Witthoft]

Thanks. I understand your answer, but could you explain how to set the
min/max values?

One way: click on the x-axis, then right-click (or cmd-click), or from
the Format menu select "Selected Axis" , then from the panel select
"Scale" .

 

[wtokyo]

Carl,

I tried to follow your instructions but I must be doing something wrong. I
created a graph and then right-clicked on the x-axis. I get the following
options:
1) Delete
2) Reset to match style
3) Change chart type
4) Select data
5) Format plot area

Please let me know if I'm missing something.