Horizontal Axis Options Changing

[JakBob]

Excel 2007

I have noticed that the horizontal Axis options can change in an Excel 2007
chart, which is driving me crazy. On some charts when I open the horizontal
axis options, under Axis options, they start out with "Interval between tick
marks", "Interval between labels" and so on, which is what I expect to see.
On another chart, with the exact same layout and data (just the numbers are
different for the data), when I right click the horizontal axis and choose
Format Axis, the Axis Options start out with Minimum, Maximum, Major Unit,
Minor Unit and so on. The options, Interval between tick marks, Interval
between labels and so on are not there. These are all simple line charts
with a date column and then 2 columns of data (numbers).

So how do I get the "normal" horizontal options back? Why is Excel changing
the Axis options?

 

[Andy Pope]

Hi,

From your description it would suggest that the chart with the
Min/Max/Major/Minor is in fact an xy-scatter chart rather than a Line chart.
Select the chart and use Chart Tools > Design > Type > Change chart type.
This should show you the current chart type.

Cheers
Andy

 

[Jon Peltier]

A related issue is that the dates used for one chart may be real numeric
dates, and the others simply text that looks like dates (or numeric dates
with at least one non-numeric cell included). In the first case, Excel would
by default choose a date-scale axis; in the second, a text category axis. On
the format axis dialog, you could try to change to the setting you want. If
this doesn't work as expected, you may have to convert the text dates to
numeric dates.

- Jon

 

[JakBob]

Thanks. I did check that and Excel shows the chart type as a Line chart.

 

[JakBob]

Thanks. I think you are right about the formatting. The problem with the
dates is they have gaps in them, such as 8/1, 8/2, 8/5, 8/8 and so on,
however, Excel insisted on filling in the missing dates on the chart itself.
In the Date column, the date cells are formatted as Dates - 8/10/2008. In
the horizontal axis properties, the Axis Type was Automatic, so I changed it
to Date. That didn't help. I then deleted the horizontal dates and
re-added them. This time, Excel is not filling in the gaps with dates,
which is good, but is still is using the wrong the horizontal axis options.

Once last thing. On one of the charts, when I recreated the horizontal
axis, it put the axis at the top of the chart, instead of the bottom. How
do I get the horizontal axis on the bottom of the chart?

 

[Jon Peltier]

If you use a date scale axis, Excel gives you a quantitative axis that uses
numerical dates. Hence the "filled in" dates. If you have a numerical axis
that goes from 1 to 5, you expect it to include all the numbers, including
2, 3, and 4. Same with a numerical Date axis.

If you don't want a numerical date axis, then you want Excel to treat the
dates as non-numerical labels, so you want a Text (or Category) axis.

Change the position of the X axis by formatting the scale of the
corresponding Y axis: change the Crosses At settings.

- Jon

 

[JakBob]

Thanks again. After some more playing around, I think I have it. Changing
the data type also changes the Axis options. I also repositioned the
horizontal axis on the bottom. The one chart is all negative numbers, with
0 at the top, so Excel put the horizontal axis at the top.