The default Excel chart legends can be awkward and time consuming to read when you have more than 2 series in your chart.
As your eye flits back and forth from legend to chart any ability to quickly interpret the data dwindles away. Just try it with the example below:
Presenting charts like this does your users a disservice and is far from best practice, yet I see it time and again, and in big name publications that should know better.
Just look at how much easier it is to interpret the chart below that has the legend aligned to the series, and color coded:
Now, it’d be nice if there was a setting we can flick on to dynamically label Excel chart series lines but alas, there isn’t. Don’t despair, there’s always a way we can wrangle Excel to do what we want [evil chuckle].
Label Excel Chart Series Lines
One option is to add the series name labels to the very last point in each line and then set the label position to ‘right’:
But this approach is high maintenance to set up and maintain, because when you add new data you have to remove the labels and insert them again on the new last data points. Ugh, tell me someone who has time for that?!
Download the Workbook
Enter your email address below to download the sample workbook.
Dynamically Label Excel Chart Series Lines
So, let’s look at how we can set up series labels that dynamically update as new data gets added, like this:
Step 1: Duplicate the Series
The first trick here is that we have 2 series for each region; one for the line and one for the label, as you can see in the table below:
Select columns B:J and insert a line chart (do not include column A).
To modify the axis so the Year and Month labels are nested; right-click the chart > Select Data > Edit the Horizontal (category) Axis Labels > change the ‘Axis label range’ to include column A.
Step 2: Clever Formula
The Label Series Data contains a formula that only returns the value for the last row of data. You can see in the image below that the formula in cell G5 is: =IF(AND(C6="",C5<>""), [@[UK Data]],NA())
As new data is added the formula dynamically fills down because my data is formatted in an Excel Table, hence the [@[UK Data]] structured reference in the formula.
Note: the reason we test that C5 isn't empty and C6 is empty is to allow for data that's still growing. e.g. imagine you had Budget and Actual data like so:
This formula ensures that the label for the Actual is at the end of the line, and as the data grows the label moves accordingly.
Step 3: Select the first label series
- Select the outer edge of the chart to expose the contextual Chart Tools ribbon tabs
- Select the Format tab (In Excel 2007 & 2010 it’s the Layout tab)
- Click on the drop down
- Select the first label series:
Step 4: Add the Labels
- Excel 2013/2016 Click the + icon beside the chart as shown below (Note: for Excel 2007/2010 go to Layout tab)
- Data Labels
- More Options
This will open the Format Data Labels pane/dialog box where you can choose ‘Series Name’ and label position; Right, as shown in the image below as shown in the image below for Excel 2013/2016 (Excel 2007/2010 has a slightly different dialog box):
Step 5: Set the font color
Select the label so the pull handles are displayed, then on the home tab set the font to bold and select the color to match the line.
Tip: Select the font color one shade darker than the line to make light colors easier to read.
Rinse and repeat steps 3 through 5 for the other series lines. It takes a bit of effort to setup but once it’s done you don’t have to do anything to maintain it.
I learnt this tip from fellow Excel MVP, Jon Peltier. There’s not much that Jon doesn’t know about charts!
If you liked this please click the buttons below to share.