Chart labels provide information related to the underlying data. In Excel, by default, chart labels can only consist of the following three – Series Name, Category Name and Chart X & Y Values.
So what about situations when you want to convey additional information – for example, say a short description about the chain of events that had a material impact on sales (or say when you want to highlight the fact that a seemingly disastrous dip in performance is actually a special case where a set of sales territories have been excluded). The default chart labels don’t offer too much of a help here. So the only option left at that point is to add a few lines next to the chart describing the chain of events (and hope that the reader reads it before you get a call with an irate voice on the other end). That, however, is not always the case.
So what if you wanted to ensure that the reader got the information right at the time when he sees the chart?
Well what about this :
What we have done here essentially is the used the chart labels to pack additional information (in this case a short description / summary of events) so that the reader is saved the additional effort of having to read the foot notes (or the embarrassment of blowing off even before reading them). How did we do it…well…here’s how
First Step – Adding a chart
First things first, dear watson…..just add a smart little chart to the Excel sheet. Before you do so, lets arrange the chart data in the format shown below:
To create the chart we will use the first two columns (in this case the Month and the Sales figures).
The end result is this neat looking chart (smart ain’t we).
However this is still without the proper labels and that’s something that we will add in the next two steps.
Second Step – Adding New Chart Series – XY Chart
We then add another series to the chart. The values that we use for this series are similar to the earlier one. The category X-axis labels remain the same and is inherited from the earlier series.
So far so good. Bear with me for another few seconds and we’ll be all done Right click on the second chart series and using the “Chart Type” option, convert the series to an X-Y dot chart. Once that part is over, we modify the source data for the second series and add the X values – which are the descriptions that we would like to show to the reader. It is these values that will be used as chart labels in the subsequent step.
Here’s how the chart will look like with the new series:
Adding Chart Labels
Right click the second chart series and turn on the chart labels. In our case, we will use the X-axis as the chart label. While at it, let’s eliminate the X-Y marker too. Once this part is done, the day’s job is done and it’s time to reap the rewards…yipee
Now some may prefer a line chart over a bar, so well here’s to the line(r) version.
You can download a sample worksheet containing chart labels with description