X axis scale on an XY scatter plot

[Tim]

Hi all,

I have created an XY scatter plot with date values on the x axis, the
problem is that my data starts in 1971 yet the x axis goes back to
1900. I cannot access the check box to make the x axis time scale as it
is greyed out in the chart options form.

I have made a bit of a mess of describing my problem but if anyone has
come accross this before or knows how to get an XY scatterplot to
automatically start its x axis values with the minimum x value given to
it, I would be grateful.

I can fix this using vba but I would rather not.

Regards,

Tim

 

[Jon Peltier]

The problem is likely that Excel recognizes your column of dates as
non-numerical labels. When an XY chart has non-numerical X values, it
reverts to using 1, 2, 3, etc. for the plotted X values. Excel's date system
starts on 1/1/1900, which is why your scale starts at 1900 with a date
format applied to the axis.

Look for non-numerical dates or blank cells within the X value range. If
your data was imported from a database, it's not unlikely that's where the
error crept in.

If the data looks like a date but isn't (remove horizontal formatting: are
the cells left-aligned? numbers are right aligned), you might be able to
fake out Excel: copy a blank cell, select the data, use Paste Special from
the Edit menu, and choose the Values and Operation - Add options. This tries
to add zero (the value of the blank cell) to the "dates", and makes Excel
try to convert them to numbers to perform the operation.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______