Excel bar chart with negative and positive values

[excelactuary]

Hello,

I have a bar graph which looks like this:

2018_06_29_15_02_03.png

The axis labels are on the bars, because of the positive and negative values. I would want to have the axis label under the bar for negative values and over the bar, for positive values. Could you tell me how to get this working? Thanks.

[rorya]

There isn't a direct way to do that, but you could simply put the axis labels at the bottom of the chart?

[MrShorty]

As noted, the built in chart options don't know how to do that. What you want can be fairly easily accomplished using a dummy series with data labels:

1) Add a column adjacent to your current data with some reasonable values (0.1, 0.1, 0, 0, 0.1, ... for example).
2) Add this column to the chart, then change the chart type for this "dummy" series to line (with markers at this point to facilitate seeing what it is doing).
3) Select the dummy data series and add data labels.
4) Format the data labels to show category name instead of value, and position the label above or below.
5) Go back to the values entered in (1) and adjust the values until the data labels are positioned where you want them. At this stage of development, I would just do it manually to see how it will work. If this will need to be done frequently, I'm sure one could figure out an algorithm/formula(s) that would automate this step, and that could be taken care of later.
6) When you are satisfied with the result, format the axis so it has no labels, and format the dummy series so it has no marker and no line.