Overlapping time (X) axis labels with auto axis options

[Paul Kraemer]

Hi,

I am using Excel 2007. I have created a Scatter chart with straight lines
and markers. My data source consists of 100 rows of evenly spaced data
points (one column for the time the date/time the data points were taken, and
a second column for the actual data points.

While I always have 100 evenly spaced data points, these data points can
span anywhere from just a few minutes to several hours. For this reason, I
have the axis options for my X (time value) axis set to Auto for the Min,
Max, Major Unit, and Minor Unit.

The problem I am having is that when my data only covers a short time
period, my axis labels overlap one another, making them unreadable. (My axis
labels are a custom format m/d/yy h:mm:ss).

I have this same setup working in Excel 2003 and I do not have this same
problem because my time axis labels are displayed on two lines (with the date
on the first line and the time on the second line). The axis settings are
somewhat different between Excel 2003 and 2007, and I cannot figure out how
to get this same behavior in 2007. If anyone can help me with this, I would
really appreciate it.

Thanks in advance,
Paul

 

[Jon Peltier]

Paul -

Yeah, I just answered another question, in which I noted that Excel 2007 is
a bit finicky about axis formatting. If it's any consolation, I just tried,
and I couldn't get the labels to wrap onto two lines.

What I've just discovered, which is a pain but it works, is that data labels
will wrap in the manner the old axis labels would. So hide the built-in axis
labels, add an extra series along the X axis (Y = 0), with a point
everywhere you want an X axis label. You can put a point at each existing X
axis tick mark, so you don't need to fake your own. Add data labels below
these points, format the data labels to show the X value (by default they
show the Y), and make sure the labels have the format you want. And hide the
added points by formatting the series to use no markers and no lines.

- Jon