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?