• WORK
• TOOLS
• TRAINING
• VIEWS
• CONTACT

Sometimes the data bars in bar charts overlap with the axis labels. This happens when the data measured changes sign from positive to negative—or vise versa—as per the chart below.

The chart shows the profit of a company from 2012 till 2020. The three year losses overlap with the axis labels.

A better solution is to keep the axis labels close to the data bars, while staggering them as per the graph below.

# HOW TO STAGGER AXIS LABELS IN EXCEL

All right. lets start.

1. First lets change the colors of the data bars. Right-click the data series in the chart and select Format Data Series from the shortcut menu.
2. On the Format Data Series task pane, click the Fill & Line icon.
3. Under Fill, select Solid Fill and check the Invert if negative option.
4. Choose two colors of your choice for the positive and negative values and the chart should look like this:

Next, I need to create two data series to label the horizontal axis. One data series for the positive labels and one for the negative. These series will represent a horizontal line at y = 0, the level of the horizontal axis.

5. Type the following formula in cell D3:
= IF(C3 >= 0,0,NA())
6. Type the following formula in cell E3:
= IF(C3 < 0,0,NA())
7. Select the range D3:E3, and then drag the handle to copy and paste the formulas into the range D4:E11.

The worksheet should look like this:

8. Next, on the worksheet select the range D2:E11and press Ctrl + C to copy the data into the clipboard.
9. Select the chart and press Ctrl + V to add the two data series to the chart.
10. While the chart is still selected, click the Change Chart Type icon in the Type group under Chart Design.
11. In the Change Chart Type dialog box select Combo under the All Charts tab.
12. Next, under the Choose the chart type and axis for your data series set the Chart Type to Line for the "+ labels" and "- labels" data series, as per the below screenshot:

13. Press the Ok button to close the Change Chart Type dialog box. The chart should look like this:

14. In the chart, righ-click the "+ labels" data series and select Add Data Labels from the shortcut menu.
15. Next, right-click the Series “+ label” Data Labels and then, on the shortcut menu, click Format Data Labels.
16. In the Format Data Labels pane, with Label Options selected, set the Label Position to Below.
17. Under Label Contains, click on Value Form Cells. In the Data Label Range dialog box set the Select Data Label Range to refer to B3:B11. Click the Ok button to close the Data Label Range dialog box.
18. Under Label Contains, uncheck Value and Show Leader Lines.
19. Next, under Text Options, click the Textbox icon and set all Margins to 0 and uncheck the Wrap text in shape option. The chart should look like this:

20. Repeat steps 14-19 for the "- labels" data series while setting the Label Position to Above and the chart should look like this:

One way to solve this problem is to right-click the axis on the chart and select Format Axis from the shortcut menu. Then in the Format Axis task pane, under Labels, set the Label Position to Low.

The chart should look like this:

Views > Excel charts > How to stagger axis labels in excel

HOW TO STAGGER AXIS LABELS IN EXCEL

21. In the chart, right click the Horizontal (Category) Axis and on the shortcut menu click Format Axis.
22. In the Format Axis pane, under Labels, set the Labels Position to None.
23. Click the Fill & Line icon and select Solid Line under Line and set the Color to Black and the Width to 1.5.
24. In the chart, right-click the "+ labels" data series and select Format Data Series from the shortcut menu. In the Format Data Series task pane, click on the Fill & Line icon and select the No Line option under Line.

Repeat step 24 for the "- labels" data series and the graph should look like this:

Finally, lets declutter the chart by deleting the horizontal gridlines, the vertical axis, and by setting the Chart Area outline to none. Add data labels, chart title, source and annotation. Adjust the data series Gap Width, change the chart font, size and color, resize the chart and the chart should look like this:

You’ll notice that the chart is dynamic and can adjust automatically. Try it. Replace 2013 with a negative value such as -14.3. As expected, the chart will adjust and will look like this:

Thank you for reading, and I hope you found this useful. If you have any questions, find me on Twitter and ask me anything.