How to Fix Overlapping Chart Labels in Excel (Positive and Negative Bars)

Step-by-step

When a column chart has negative values, the bars run into the axis labels. This trick staggers the labels: years with profits get their label below the axis, years with losses get it above, and it updates by itself. The example shows ten years of profit for a tech company, with losses in 2019 to 2021.

1. Insert a column chart

Select the table (B2:C12), go to Insert > Recommended Charts and choose Clustered Column. The three negative bars cover the year labels. (Setting the axis Label Position to Low moves all labels to the bottom, but staggering them keeps each label next to its bar.)

2. Colour gains and losses

Right-click a bar, choose Format Data Series > Fill & Line > Solid fill and tick Invert if negative. Pick green for positive values and red for negative ones.

3. Add two helper columns

These act as invisible rails along the zero line, one for each set of labels. Name them +labels and -labels (type an apostrophe first, ‘+labels, or Excel treats the plus sign as a formula). In D3:

=IF(C3>=0,0,NA())

In E3:

=IF(C3<0,0,NA())

Each shows 0 only for its own kind of year. NA() returns #N/A, which a chart simply skips. Copy both down.

4. Add them to the chart as lines

Copy the two helper columns with their headers, click the chart and paste. Go to Chart Design > Change Chart Type > Combo and set both helper series to Line. Two flat lines appear along the zero axis.

5. Label the lines with the years

Right-click the +labels line, choose Add Data Labels, then Format Data Labels. Set the position to Below, tick Value From Cells and select the years (B3 down). Untick Value and Show Leader Lines. Under Text Box, set all margins to 0 and untick Wrap text in shape. Do the same for the -labels line, but set the position to Above.

6. Hide the original labels and helper lines

Format the horizontal axis: set Label Position to None and give the axis a solid black line about 1.5 pt wide. Set both helper lines to No line: the labels stay, the lines vanish.

7. Tidy up

Delete the gridlines and vertical axis and remove the chart border. Add data labels to the bars, a title, and any notes you need, and set the gap width to about 50%. Change a value from positive to negative and the bar colour and label position switch automatically.