Sign in or start a free trial to avail of this feature.
6. Adding Labels to Column Charts
Labels can be used on column charts instead of a vertical axis. However, they should only be used when you have a small number of data points on a chart.
Adding labels to column charts
- Charts have a vertical axis by default when created in Excel
- For column charts with only a few columns, data labels are better than a vertical axis
How to add data labels
1 Select a data series with a single left-click
2 Right-click and Add data labels
3 To edit data labels, select a label, right-click and Format data labels
If you have only a few bars on your chart it might be worth adding data labels to your chart rather than have a vertical access. I much prefer to use Data Labels because I always want to know the exact size of the bars rather than having to eyeball it from the vertical axis. To add data labels, just right click on a data series and click Add Data Labels. To see the data labels clearly, I'll need to select them and change their color to white. The data labels are determined by the vertical axis of your chart. Currently, the vertical axis shows millions, therefore, my data labels are shown in millions as well. If I change my vertical axis to say thousands by changing the display unit, the labels will change accordingly. I'll now switch this back to millions. If we'd like to show the data labels in millions but with one decimal point, we can select the data label, right click, and Format Data Labels. I'll then go to Number, switch the number category and then reduce the decimal places to 1 and then close. Let's now repeat this for the second data category. So I'll Add Data Labels, change the color to white, and then right click, Format Data Labels, go to Number and include one decimal point. And off camera, I'll repeat for the last two data series.
So that we don't have to perform this action for future charts, I'll select the chart and also save this as a template which will save my data labels in this format. And I'll simply save over the existing template that we saved in the previous lesson. If I now want to apply this template to another chart, I'll select that chart, go to Change Chart Type, Templates and select my template. This moves the labels to the middle of the bar. It puts the axis in millions and it makes sure that the color is the same as the bottom color in my original chart. Now that we have the labels in place, we can remove the axis because it doesn't really serve our purpose. To change the position of the label, again we'll select the labels, right click and format data labels. And in the label position option, we can put the label Inside the End, Inside The Base or At the Center.
Typically for stacked column charts, we'll leave the label At the Center. If you'd like to put the label on top of the bar, unfortunately, the stacked column chart won't do. We'll need to change the chart type to the Clustered Column Chart. So I'll escape, right click, Change Chart Type and select the Clustered Column Chart. Now I'll select my data labels, right click, and Format Data Labels. And here we have an option for Outside End under Position. I'll select it and then click Close. I'll now change the color of my labels to a dark blue. And now we have the labels positioned above the bars just as we'd like. Sometimes you may need to change the position of one particular label, particularly if the bar is very small. To do this, simply select all the labels by clicking once and isolate the label by clicking twice. You can now move this label within the chart to a position that suits your needs.
I'll now undo with Ctrl + Z. For charts with less than 10 data points, I find labels to be more useful than a vertical axis.
Consider using them the next time you have a chart of this type.