7. Adding Labels to Other Charts
In this lesson, I'll show you how to add labels to add labels to other chart types including pie charts and bar charts. I'll also show you when not to use labels.
Adding labels to pie-charts
- Pie charts have multiple label options
- However, the label option that should always be used is Percentage
- Conveniently, pie charts can automatically calculate percentages from input values
Adding labels to 100% stacked column charts
- 100% stacked column charts don't automatically calculate percentages
- Unfortunately we need to calculate these percentages manually
- Once the percentages are calculated, switch the chart input data to these percentages
F4: Anchor cells
ALT + E , S , F: Paste formulas
Column charts are very suited to data labels because the text can be read very easily from the chart. Labels can actually be applied to all types of charts but only work well in certain instances, which I'll show you in this lesson. For charts that hold a limited number of data points such as a pie chart, labels can work very well.
In this scenario, I have a pie chart that shows the revenue breakdown for 2003. I'll right click on one of the segments and add data labels. When I right-click and add data labels, the actual revenue figures are shown in each segment. I'd much prefer this to be the percentages. Thankfully, pie charts can automatically calculate the percentage for us. Select the data labels, right click and Format data labels. In the label options, we'll remove the values and we'll add the percentages.
And when I escape from this dialogue box you can see that the percentages have been added to our pie chart. We can format these labels by changing their color to white and increasing their size to say 16.
And this now makes our chart a lot more readable. 100 percent stacked column charts can also benefit from data labels. In this example, I have a 100 percent stacked column chart for the revenues from 2009 to 2013. Unfortunately, these values are showing the full revenue figures. And again, I'd like to show the percentages. The stacked column chart does not have a percentage option in the data labels dialogue box. If I right-click and format data labels, I can't find the percentage option in this menu. Instead, I'll have to calculate the percentages manually. So returning to our dataset, I'll select the North American revenue and divide by the sum of all revenues. And I'll anchor this array vertically. I'll then copy and paste for the remaining three cells. And I can actually copy and paste for the remaining four years as well. All I need to do now is select the chart and move this selected data to my percentages array. And this now makes our stacked column chart much easier to read. For 100 percent stacked column charts, always try and display your information in percentages rather than absolute values, even though Excel doesn't give you this option automatically.
For horizontal bar charts, labels can also work very well, particularly when they're placed at the end of the bar like so. For line charts, however, where you have lots of data points, in this case, I have over a 100 data points, when we add data labels, it actually makes our chart impossible to read. So for charts with a large number of data points you're probably better off sticking to the vertical axis.