format axis grayed out

[cporter]

I have charts I do for trend data. In Excel 97 these charts worked
flawlessly and I could adjust the axis scales to make it all legible.
Since migrating to 2000 these charts no longer work as before. The x
axis usually has way to many markers and the text all runs together. I
can no longer go into the format axis dialog and change it the way I
want to, the option is grayed out. What has changed and how do I get
around it? A small sample of the date is below. The typical datasheet
hold 5900+ records and a chart might use 1400 or more records in the
chart.



DATE TIME ZONE 1A ZONE 1AA ZONE 1B ZONE 1C ZONE 1D LOWER SPEC UPPER
SPEC
10/31/2005 1:00:00 68.1 68.2 68.0 68.0 68.1 67.0 69.0
10/31/2005 2:00:01 68.1 68.0 68.0 68.0 68.1 67.0 69.0
10/31/2005 3:00:00 68.0 68.1 68.1 68.1 68.1 67.0 69.0
10/31/2005 4:00:01 68.0 68.0 68.1 68.1 68.0 67.0 69.0
10/31/2005 5:00:00 68.0 68.0 68.0 68.0 68.0 67.0 69.0
10/31/2005 6:00:00 68.0 68.1 68.1 68.0 68.0 67.0 69.0
10/31/2005 7:00:00 68.0 68.1 68.2 68.0 68.1 67.0 69.0
10/31/2005 8:00:01 68.0 68.0 68.0 68.0 68.1 67.0 69.0
10/31/2005 9:00:00 68.0 68.0 67.9 68.0 68.0 67.0 69.0
10/31/2005 10:00:00 68.0 68.0 68.2 68.0 68.1 67.0 69.0

 

[HS Hartkamp]

Since migrating to 2000 these charts no longer work as before. The x

axis usually has way to many markers and the text all runs together. I
can no longer go into the format axis dialog and change it the way I
want to, the option is grayed out. What has changed and how do I get
around it?

Difficult to say without seeing the xls-file.
Could it be possible that the x-axis has a different setting (categories vs.
date vs. linear scale) ? This is part of the graph-options (right-click
somewhere in the white area of the graph; I believe it's the fourth option
in the quick-menu).

This is a setting of a higher level than the formatting of the axis, so it's
possible that this makes the scaling-options be greyed-out. If you want me
to take a closer look, send me the file (remove the dot and the capitals
from the e-mail address)

Bas Hartkamp.

 

[cporter]

It is something specific to the newer versions of Excel. The charts
work perfectly with the data in Excel 97. If you insert the data I
included previously into a spreadsheet and chart it (line chart) you
will see the axis options are locked out when you chose the date AND
time column for the x-axis. If you chose only the date column or only
the time column the axis format options return. As mentioned, this
wasn't the case with Excel 97.

 

[HS Hartkamp]

I have no experience with two category-axis. It seems that Excel
automatically changes the behaviour to 'labels' (=categories, as opposed to
values / time scale) and concatenates the two values to one text-string.
Seems that it is too difficult to understand that the dates and the times
belong together.

My solution would be to insert an extra column to add the date and the time
together, and use that extra column to base the lines on.
That still does not solve the graph, but when you change the graph-type to
x-y-scatter, then you can obtain a neat horizontal axis. Make sure the
number format of the axis is set to include the desired date/time format.
Drawback is that you cannot see the dates when you scale the axis (i.e.
you'll see 38655, being the internal representation of the date), but when
you adjust these values you can still type a date-format. Excel will change
that to the 5-digit number.

If you're not familiar with this internal representation (you probably are,
but just in case you're not):
Excel stores dates as (integer) numbers, 0 = 0 january 1900, 1 = 1 jan 1900,
38655 = 31-10-2005. Fractions represent the time: 0,25 = 6:00 hours, 0,5 =
12:00 hours, 0,6 = 14:24 hours. In this way you can easilly calculate with
dates (e.g. calculate differences between dates in days)
I'm not sure it can perform calculations with pre-20th century dates

Bas Hartkamp