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 t0 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 Chart Utility

Comments

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

    Otherwise, impeccable.

  2. Jeff -

    Caught me! Actually I used:

    =IF(data<0,10,-10)

    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.

Subscribe without commenting

Trackbacks

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

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

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites