Axis Labels That Don’t Block Plotted Data

Someone on Twitter asked me how to make axis labels stay on the side of the axis away from the data. This bar chart illustrates the desired outcome. When bars indicate positive values, the labels are on the negative side of the axis, and vice versa.

Axis labels that do not block the data

The Problem

This data and chart show the behavior of axis labels in Excel. Even though some data is negative, the labels are always on the negative side of the axis, where the labels overlap with the bars. Depending on the series fill color, this could make the labels illegible.

Default axis labels overlap negative data

Lame Attempts

You could play with axis formats to try to make things better. You could format the horizontal axis to make the vertical axis cross at value other than zero; I used -25 below left. This is a terrible solution, though, because now the bars don’t start at the baseline of zero, and even the negative values are plotted with positive direction bars. No good.

You could format the vertical axis labels to make them appear in the Low position, that is, below the lowest values on the horizontal axis, below right. In many cases this is appropriate, but it’s not ideal.

Workarounds to prevent axis labels from obscuring the data

The Solution

The trick is to realize that you can’t make the built-in axis labels do what you want, then think of another way to show the labels. In this case we’ll add a hidden bar chart series, which will use its own data labels.

I’ve added a column with small negative values next to positive plotted values, and small positive values next to negative plotted values.

Data for dummy axis label series

When you add the new data to the chart, the built-in axis labels still overlap with the negative bars (left). The bars are offset, but we can fix this by formatting either of the bars and changing the overlap to 100% (right).

Adding custom axis labels

I hid the built-in axis labels (left). Then I added data labels to the added series (right). The default labels show the plotted values (10 or -10), and I selected the inside base position for the labels.

Adding custom axis labels

The charts below show the four positions for data labels in clustered column and bar charts. Center means in the center of the bars. Inside Base means inside the bar next to the base (bottom) of the bar (next to the axis). Inside End and Outside End mean inside and outside the far end of the bar. Stacked charts can’t have Outside End labels, because these would overlap with any bar stacked on top of the one being labeled.

Data label positions in Excel bar and column charts

I’ve formatted the labels so that they display the category names instead of the values (left) Then I hid the dummy bar chart series by setting its fill to No Fill (right).

Adding custom axis labels

Data labels are not allowed to be as long as axis labels: note that the Omicron label is wrapped onto two lines. For general clarity it’s a good idea to keep your labels short, but here it’s doubly important.

Excel 2013 has finally enabled users to change the size of data labels (but not yet chart and axis titles). However, I’m using Excel 2010 right now, so I don’t have that luxury.

Since the data label size is related to the chart dimensions, you can make the label fit onto one line by stretching the chart.

Axis labels that do not block the data

If your chart size is constrained, you can change the font instead. Below left I’ve changed the Calibri labels from 10 to 9 points. Below right I’ve changed from Calibri 10 points to Arial 8 points.

Axis labels that do not block the data

For this chart, I think I’ll keep Calibri 9 point labels.


Peltier Tech Charts for Excel


  1. What? Not dynamic? You must be getting old, Jon. ;-)
    How ’bout a little sign language:

    Otherwise, impeccable.

  2. Jeff –

    Caught me! Actually I used:


    So I get a bar even for a value of zero; and this formula places the label for zero to the left of the axis.

  3. Nice one Jon! I’ll be stealing this idea for sure.

  4. Awesome technique Jon! I also like your Bar Chart Data Label Positions image. Thanks for sharing!

  5. This is really awesome! Thanks for the post :-)

  6. Really nice trick. Very impressive.
    Thanks for sharing! :)

  7. Jagannath Patra says:

    Thank you Jon. It is a nice trick.

  8. Chandeep Chhabra says:

    Hey Jon, its a killer!

  9. Awesome trick…very useful

  10. Thanks for posting this. It’s very helpful. I was curious how to format the labels so they show the values and not the category names? Is it even possible?

  11. Heather –

    Select the labels, format (Ctrl+1 is the shortcut) and select the appropriate option.

  12. The Original Joe S says:

    “Excel 2013 has finally enabled users to change the size of data labels (but not yet chart and axis titles). However, I’m using Excel 2010 right now, so I don’t have that luxury.”
    Thanks. I couldn’t figure out why I could not resize the label box under a line chart.

  13. Is this at all possible in Excel 2007? I can’t figure out how to hide just the original labels and add the new labels.

  14. Brian –

    Sure it’s possible, all the way back to Excel 97, if not earlier. Follow each step slowly and you’ll get it.

  15. And here is another way. – Simpler in some cases

    Make your graph a stacked column (or row) chart and add a small series that has a small fixed value in the opposite direction to your data. So if your data is in column B2:Bxx add a column C which has the formula =-sign(B2)
    You may need to scale it up by some small amount to match your graph sizes?

    Now plot those two series as a stacked column or row (you may need to adjust the order?). Put a label with a value equal to the category in the middle of the new series and then you can set the label size as you want and finally set that series to have no-shadow, no-fill and no-line. Finally delete the series from the legend. The labels will sit on or near the axis and on the opposite side to the values.
    Interestingly in this example using Sign() you get a label on the axis if the value to be plotted is actually zero!


    Example at

    As they say there are more ways of killing a cat than … insert your favourite phrase here.

    Bob Jordan


  1. […] Axis Labels That Don’t Block Plotted Data | Jon Peltier […]

  2. Data Viz News [53] - sqoops says:

    […] Axis Labels That Don’t Block Plotted Data | Jon Peltier […]

Speak Your Mind


Peltier Tech Charts for Excel 3.0