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.
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.
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.
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.
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).
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.
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.
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).
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.
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.
For this chart, I think I’ll keep Calibri 9 point labels.
Jeff Weir says
What? Not dynamic? You must be getting old, Jon. ;-)
How ’bout a little sign language:
=sign(data)*-10
Otherwise, impeccable.
Jon Peltier says
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.
Mike Alexander says
Nice one Jon! I’ll be stealing this idea for sure.
Jon says
Awesome technique Jon! I also like your Bar Chart Data Label Positions image. Thanks for sharing!
Rohit says
This is really awesome! Thanks for the post :-)
MF says
Really nice trick. Very impressive.
Thanks for sharing! :)
Jagannath Patra says
Thank you Jon. It is a nice trick.
Chandeep Chhabra says
Hey Jon, its a killer!
sreekhosh says
Awesome trick…very useful
Heather says
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?
Jon Peltier says
Heather –
Select the labels, format (Ctrl+1 is the shortcut) and select the appropriate option.
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.
Brian says
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.
Jon Peltier says
Brian –
Sure it’s possible, all the way back to Excel 97, if not earlier. Follow each step slowly and you’ll get it.
Bob Jordan says
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!
Voila!
Example at https://dl.dropboxusercontent.com/u/2835140/AxisLabelsDontBlockPlottedData.xlsx
As they say there are more ways of killing a cat than … insert your favourite phrase here.
Bob Jordan
Rogier de Wit says
This is brilliant and really helped, thank you very much!