center labels in x axis while skipping 3 of every 4 categories

[HGood]

I have a line chart that plots percent progress (0% to 100% up the Y axis)
over a 10 year period on the X axis. The data is quarterly. But I only show
tick marks and labels for every fourth quarter, in other words, my labels
are Years, e.g 2003 2004 2005 2006 2007, etc. I only show gridlines
for the Years, not quarters. I am using Category as my main axis, nothing
to do with Time or Date.

I would like to center the Year labels BETWEEN the tick marks (gridlines).
But when I click the "Value (Y) axis crosses between categories", it seems
to shift it slightly to center under a Quarter (even tho the Quarter tick
marks don't show), but doesn't center the label between the Year tick marks.

So altho Excel seems to allow me to not show the minor gridlines (quarters),
and I can indicate in "Number of categories between tick-mark labels" 4, it
doesn't allow me to center the labels between every fourth one.

Or perhaps there is a way to do this.

In summary, where I have labels like this:
2003 2004 2005 2006 2007 2008 2009 2010
and each of these labels has 4 categories of data represented between them
(individual quarters) that aren't shown by gridlines,
I would like to have a single tick mark and associated vertical gridline
BETWEEN these Year labels. Right now, they do exist, but they aren't aligned
correctly.

I guess in reality, the gridlines, showing only every fourth category, are
actually on Quarter 1 of each year.

Any help would be very much appreciated.

Thanks,

Harold

 

[Jon Peltier]

Harold -

As I pointed out in another thread yesterday, if your data looks like
this, with quarters in one column and years in the previous column:

Data
2000 Q1 1
Q2 2
Q3 3
Q4 4
2001 Q1 5
Q2 6
Q3 7
Q4 8
2002 Q1 9
Q2 10
Q3 11
Q4 12
2003 Q1 13
Q2 14
Q3 15
Q4 16

you can use the first two columns as your category labels. If the cells
above the years and the quarters are blank, Excel will automatically do
this if you select the entire range including the blank cells for your
source data range. Also, the cells between years in the first column
must be blank. "Blank" means nothing in the cell, not even a formula
that returns "" (an empty string). The axis looks like this:

Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
2000 2001 2002 2003

If you don't have a quarter column, but just the year and the data,
Excel puts the year in the category axis slot under the data point from
the same row. You need a multiple column category range to make it work.

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

 

[HGood]

Jon,
Thanks so VERY much. I didn't realize I could have two rows of labels below
the X axis. You finally got thru to my thick head!

Now, if I find that that row of Q1, Q2, Q3, Q4 lables clutters things too
much, is there a way to hide it and only show the Year labels?

Thanks again,

Harold

 

[Jon Peltier]

Harold -

I tried this several ways, and as soon as the chart realized the quarter
labels were absent, the years went back to the undesired positions.

You could keep the axis tickmarks where they are, but hide the tick
labels (check None in the appropriate place on the Format Axis -
Patterns dialog). Then add a helper series to put the year labels where
you want them, following the arbitrary axis technique:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

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

 

[Debra Dalgleish]

Instead of entering the Q1, Q2 labels on the worksheet, type: =" "
in those cells. The lines will appear on the chart, but not the labels.

 

[Jon Peltier]

Unfortunately, there is a conspicuous blank space where the quarter
labels would go, to make room for the spaces. Or worse, the tick marks
hang down a long way between the plot area and the year labels.

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