X-axis

[Guest]

Is it possible for the label on the x-axis to adjust to the top or bottom on
weather or not the bar graph is negative or positive. For example, I have
pivot chart that has both positive and negative values on it. I would like
all the positive labels to be below the zero line, and all the negative
labels to lie above the 0 line. Any help?

 

[Jon Peltier]

You can't format the "official" axis labels this way, but you can fake it
with a dummy axis, using one of these techniques and examples:

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

Since you won't have to construct a custom scale, your task is easier. You
should hide the regular labels, then add two XY or Line series, each having
a point along the axis where you need the labels. Make two ranges in the
worksheet, one for labels above the axis, and one for those below; you can
use formulas to show either the label or "" depending on whether the value
is positive or negative. Then use one of these labeling programs to get
these labels onto the dummy series you've added (both Excel add-ins, free to
download, and easy to use):

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

Finally, hide both added series by setting the line and markers to None.

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

 

[Andy Pope]

Hi,

This is not a builtin option. What is built in is to have the axis
labels displayed at the bottom of the plot area rather than on the axis
if your problem is really just the negative bars going through your labels.

Otherwise you could add a dummy data series and use the data labels to
act as axis labels. See Jon Peltier's explanation of how to build an
arbitrary axis.
http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

Cheers
Andy