X axis scale on an XY scatter plot

T

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
 
J

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
_______
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top