align labels on X axis

[HGood]

I already have a line chart and I already had the "Y axis crosses between
categories" box checked. When I uncheck it, the labels move only about 1
digit to the left. The labels are years, i.e. 2005, 2006, 2007, etc. So
clicking the box only shifts the labels by about one digit (one of the zeros
in these labels).

Might it have something to do with the format of the label source, that is,
if it is formated as a number vs a date?

So, the question is, why does it only shift by one digit to the right when I
check the "Y axis crosses between categories" box instead of centering
nicely between the vertical lines?

Thanks for any help someone can offer.

Harold

=================
Harold -

In a line chart, you can format the axis, and on the Scale tab, check a
box that says "Y axis crosses between categories". You can't do this
readily in an XY Scatter chart. To switch, select the chart, choose
Chart Type from the Chart menu, and choose the line style you want.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jon Peltier]

Harold -

Could you paste several lines of your data into a reply? Also, double
click on the axis, select the Scale tab, and report what it says right
below the tabs: Category (X) axis scale, or Time (X) axis scale. If it's
a time axis, what are the base unit and major unit?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[HGood]

Jon,

When I double click it says nothing about Time. It says "Category (X) axis
scale". Perhaps the Time is if it is a Scatter chart, this is a Line chart.
Under this same Category X axis scale label I have:
Value Y axis crosses at cat #: 1
# of cat between tick-mark labels: 1
# of cat between tick marks: 4
Also, I have checked the "Value (Y) axis crosses between categories" box.

I want annual labels, although the data is in quarters (4 per year).

Here is some data:
Year Qtr Qtr prorated Planned Cumm. Qtr Actual Qtr Cumm-lative

<2005 0.0% 0.0% 8.4% 8.4%
0.0% 0.0% 8.4% 8.4%
0.0% 0.0% 8.4% 8.4%
0.0% 0.0% 8.4% 8.4%
2005 2005 Q1 1.0% 1.0% 3.2% 11.6%
2005 Q2 1.0% 2.1% 0.9% 12.5%
2005 Q3 1.0% 3.1% 0.0% 12.5%
2005 Q4 1.0% 4.1% 0.0% 12.5%
2006 2006 Q1 1.3% 5.4% 2.3% 14.8%
2006 Q2 1.3% 6.6% 0.9% 15.7%
2006 Q3 1.3% 7.9% 1.4% 17.1%
2006 Q4 1.3% 9.1% 0.0% 17.1%
2007 2007 Q1 2.4% 11.6% 0.5% 17.5%
2007 Q2 2.4% 14.0% 0.0% 17.5%
2007 Q3 2.4% 16.5% 1.2% 18.8%
2007 Q4 2.4% 18.9% 3.0% 21.8%



Harold

 

[Jon Peltier]

Harold -

When your data is arranged to look like the following, the axis comes
out very nice:

A B C D
2005 Q1 1.00% 1.00% 3.20% 11.60%
Q2 1.00% 2.10% 0.90% 12.50%
Q3 1.00% 3.10% 0.00% 12.50%
Q4 1.00% 4.10% 0.00% 12.50%
2006 Q1 1.30% 5.40% 2.30% 14.80%
Q2 1.30% 6.60% 0.90% 15.70%
Q3 1.30% 7.90% 1.40% 17.10%
Q4 1.30% 9.10% 0.00% 17.10%
2007 Q1 2.40% 11.60% 0.50% 17.50%
Q2 2.40% 14.00% 0.00% 17.50%
Q3 2.40% 16.50% 1.20% 18.80%
Q4 2.40% 18.90% 3.00% 21.80%

There are two blank cells to the left of "A", and blanks in the first
column between the years. These blanks have to be true blanks, not
formulas that return "". Also, there should not be a blank row between
the header row and the data; also no blank column between header column
and data. When you select this entire range and create a Line or Column
chart, Excel does two things:

1. Detects the two blank cells (one blank row and two blank columns) and
allocates the top row for series names and the left two columns for
category labels.

2. Detects the blanks between the years, and centers the years under the
four quarters to fill the blanks.

Your data seems to have an extra column of years, which might be
confusing Excel.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______