Excel Line Chart – Legend Versus Data Label
Here is a great example where a line chart could be improved with simply labeling the lines in the chart versus using a legend. The cross reference between the chart and legend is too time consuming and can tend to frustrate the reader. This blog isn’t a forum on Excel, but I do think this tip will help some people to think outside of the default charts in Excel.
I created a simple line graph using different data to illustrate how easy it is to add labels instead of a legend.
First, click on the last data point in the series and when you have only the last one selected, right-click on the last data point and select Format Data Point… Be sure to only select the last data point and not the whole series.
Next, select the second tab called Data Labels. Click off all the buttons except the Series Name option.
This is the result with the series labels shown to the right of the last data point. If you want to tweak the positioning of the label (because they may show on one of the grid lines, then you can simply click on the label and move it around.
The benefit of a label versus a legend is there is no time wasted cross referencing between the chart and legend. It would be irresponsible of me to not explain the challenges of using this option. As you can see below, if you add more data points, the labels are static and do not move. Also, another downside to data labels is if two data series end at the same point or value. Below you can see what happens when an additional month is added to the chart.
Most Commented Posts
October 16th, 2008 at 12:16 pm
This was item 9 in my 9 Steps to Simpler Chart Formatting, and I subsequently shared a VBA procedure to Label [the last point of] Each Series in a Chart. The procedure is good for changing data: it starts with the last point of each series, adds a label, then strips away any labels on earlier points.
October 17th, 2008 at 2:48 pm
@Jon. Thanks for sharing the links. Your list of 9 steps is excellent and number nine does talk about this issue.
I like the follow up post on labeling the last point of a series using VBA script, but I think that will be beyond most casual users of Excel. I would venture to say that 90% of Excel users wouldn’t know what VBA even stands for.
I think a good resource on VBA is a book called: Excel Power Programming with VBA.
Do you or anyone else know a good resource for learning more about VBA for the readers here?
October 17th, 2008 at 3:50 pm
IMO it’s better to use a book, but that might just be my learning style. John Walkenbach’s books (Power Programming is his) are well written, with good examples, and clear explanations.
When I have a specific question, I’ve found that Google is a good resource.
December 22nd, 2008 at 10:45 pm
Playing catch up again, but I agree that Walkenbach’s books are very approach for Excel users at all levels. VBA is often perceived as an advanced topic, but a few simple scripts — including the chart series labeler Jon posted her — ease the pain of tedious tasks and excelerate the users skill-level.
To quote you, Tony: “[T]ry new things and practice them to improve your value over time! There are always opportunities to learn new ideas, approaches, tactics, etc. to add to your toolkit.”