multiple x axis labels

[Mwise]

I have a bar chart where the x-axis has multiple levels of data and a rank
value that corresponds to the data series that I want to plot. That rank
value needs to be displayed on the x-axis with the category name and
sub-category name. My dataset looks like this:

Category Sub-Cat PriceRank Price
Fruit Apple 1 $15
Fruit Banana 2 $10
Fruit Pear 3 $5
Veg Onion 1 $2
Veg Potato 1 $2
Veg Green Beans 3 $1

I was able to get this working by merging some cells and such. But the two
problems are that the Sub-category "Green Beans" always gets turned into two
lines now matter how much I juggle the font size and offset. The x-axis
labels are rotated 90degrees and there is a lot of room for them to fully
display without breaking over two lines...but Excel does it anyway. I can't
justify the x-axis vertically either.
So I tried putting the rank into the graph as a dummy series, but then I
lose my sub-category names and can't get them back.
Is there anyway to either: vertically size/align the x-axis to stop the
line breaks? Or some way to add dummy series with multiple x-axis labels?

 

[HEK]

Hi:
Frankly, I am a bit confused what you want. But if you want to display
three levels of x-axis labels (Fruit/Veg - Type - Rank) then I think you can
do following.
Assuming yr data are in A17, remove A3,4 and A6,7 (so 2x Fruit and 2x
Veg). Format yr rank as text (or replace them by A,B,C). Now select A17,
and follow the wizard. It shd display cat.axis labels with closest to the
axis the rank, below the type (separated by long lines) and below that
"Fruit" resp "Veg" arching the three types above, vusalized by a separating
longer line. The price is the displayed data series.
HTH,
Henk

 

[Mwise]

Hi:
Frankly, I am a bit confused what you want. But if you want to display
three levels of x-axis labels (Fruit/Veg - Type - Rank) then I think you can
do following.
Assuming yr data are in A17, remove A3,4 and A6,7 (so 2x Fruit and 2x
Veg). Format yr rank as text (or replace them by A,B,C). Now select A17,
and follow the wizard. It shd display cat.axis labels with closest to the
axis the rank, below the type (separated by long lines) and below that
"Fruit" resp "Veg" arching the three types above, vusalized by a separating
longer line. The price is the displayed data series.
HTH,
Henk

Yes, that's what I did originally did, but the three level of x-axis labels
couldn't be justifed top/bottom/middle when rotated 90degrees and wrapped to
two lines. I finally gave up with trying to do two x-axes and multiple
series and came up with a hack of adding a x-axis title with the most generic
categories at the bottom, spaced out with blanks. That only worked because
those categories were always fixed instead of variable like the
sub-categories and the ranks. Also turning off Auto-scaling helped with
getting the x-axis label onto one line.

Thanks,
M