In Data Sets, Dick described his affection for data to his Daily Dose of Excel readers. One of his bar charts showed the life expectancy differential between men and women in various countries. In Bangladesh, men actually outlive women, so the bar extends into negative chart space, and the category label “Bangladesh” overlies this data.

Dick wondered how to avoid this problem. There are two ways, one very simple and the other slightly simple but more attractive.

I’ll illustrate with the following data. The “opposite” data in column C is dummy data for a hidden series which will provide data labels that replace the default axis labels. The values are defined by a simple formula: =-B2/ABS(B2). I could have used =-SIGN(B2), but I thought of this variation after I built the data range.

I created a bar chart using the first two columns of data.

Since Excel plots data starting from the axis and moving away, the first category (alpha) is plotted next to the axis, below the next category. It’s actually very logical, but people don’t understand this. Anyway, fix this by formatting the category axis so it is plotted in reverse order, and the value axis crosses at the maximum category, which is  at the bottom of the chart when categories are plotted in reverse order.

Okay, looks fine, except for the label “gamma” which overlap its data point.

I promised an easy way, so here goes. Format the category axis so the axis tick labels are in the “Low” position. They are plotted at the low side of the chart. This is a good quick way to move the labels out of the way, but in some cases, the labels may be moved pretty far from the axis. Because the labels need a margin, the entire chart is compressed laterally, reducing the resolution of the value axis scale.

So here is the almost as simple method, which eliminates the issues raised above. Extend the chart source data to include the “opposite” column of data.

Format either of the bar chart series so the overlap is 100%. Now the bars line up perfectly.

Format the category axis to remove the tick labels (select the “None”) position.

Add data labels to the “opposite” series, using the Category Labels option.

Format the data labels so they are in the “Inside Base” position.

Now hide the “opposite” series by formatting it to show no border and no area fill. Also remove the “opposite” legend entry by single clicking twice on its text label, and pressing Delete. (You could remove the legend altogether, since there’s but a single series.

The technique works just as well with column charts.

You could also use the Invert Excel Chart Format if Negative technique to further highlight the negative value.

Related Posts:

• Individually Formatted Category Axis Labels
• Area Chart – Invert if Negative
• Add a Target Line
• Individually Formatted Dual Category Labels
• Draw an Axis With Tick Marks But No Line
• Growth Rates in a Panel Chart
• Fill Between XY Chart Series (XY-Area Combo Chart)
• Legends in Excel Charts – Formats, Size, Shape, and Position
• Magazine Quality Chart (Economist)
• Fill Below an XY Chart Series (XY-Area Combo Chart)