My friend Stephanie Evergreen–who co-hosts the awesome Rad Presenters Podcast with me–published a post last week about labeling charts in Excel. In it, she offered some guidance about how to get rid of your default legends and directly label your charts in Excel.
Stephanie’s post is consistent with a general strategy I use and refer to as “integrating text and graphics.” The idea is to create more effective visualizations by merging explanatory text and the visualization itself. For most of us, the easiest way to do this is to integrate our text and graphics and directly label our visualizations. Once you’ve had some success on that front, you can push forward to directly annotating your visualizations; providing explanations, labels, and additional content.
But I digress. Stephanie’s showed two ways to directly label a line chart in Excel. Method #1 used the new labeling feature in Excel 2013. In Method #2, she inserted text boxes in the graphic; this approach would work in just about any version of Excel.
Let me offer two alternative ways to directly label your chart.
Instead of adding text boxes–which I find will never go exactly where I want them and can sometimes get lost as I copy and paste from Excel into another program like PowerPoint–add additional data to your chart and use data labels for those extra series. Here’s an example line chart. In it, we have six series with ten observations each. If you plot these data and add data labels to each line, you’re going to have a complete mess.
But, adding another set of series with only a single data point–the location at which you want to place your label–you’ll only have one label and it will go exactly where you want it. You can now create the chart with all 12 series (six original plus six for the labels), select each series, add the data label, and then format the label.
Note that the default data label is the value of the data point. In this case, we want to use the name of the series, so you have to go into the Format Data Labels menu and deselect Value and select Series name.
After formatting each label, you can delete the legend and style the gridlines, tick marks, etc. Here’s a final version:
You can now easily modify the location of the data labels by changing the data. As an example, I moved the labels to the 8th position in the image below by moving the data values.
The overarching message here is to try to encode all of your data, even for labels. That way everything goes exactly where you want it, you can move the labels around without having to reformat everything, and you can easily reuse the chart for completely different data.
The above method–which works just the same on Macs and PCs–is a bit labor intensive. You need to add additional data series, select the point, add a data label, and format that data label. An easier way to do this is to use some Visual Basic for Applications (VBA) code. (Here’s a decent Getting Started guide from Microsoft).
I can’t remember exactly where I got the framework for the code below (Jon Peltier definitely gave me some tips when I first started out trying to learn VBA) but this will work easily on PCs. (I’ve had mixed results with this on my Mac and I’m not sure why.)
Sub LabelLastPoint() Dim mySrs As Series Dim nPts As Long For Each mySrs In ActiveChart.SeriesCollection With mySrs nPts = .Points.Count mySrs.Points(nPts).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False mySrs.Points(nPts).DataLabel.Text = mySrs.Name End With Next End Sub
I’m not going to walk you through how the code works, but I will show you how to use it in Excel 2010. If you’re not a VBA wizard, don’t worry, neither am I. To use this code, you need to activate your Excel Developer toolbar. To do so in Excel 2010, go to File and then Options and select Customize Ribbon. On the right side, you’ll see the Main Tabs menu, where you can check the box for Developer (directions for Excel 2013 can be found here). (On the Mac, select Preferences under the Excel menu bar, and then select the Ribbon option in the bottom-right part of the menu. You can scroll down from there and find the Developer tab option.)
Click Okay and you should now see the Developer tab in your Excel ribbon. Go back to your worksheet, select the Developer tab, and select the Macro button (second button from the left). In the pop-up box, type in “LabelLastPoint” and select Create.
In the resulting VBA editor, you can select the text that’s there and overwrite it with the bit of code shown above. Step out of the VBA editor, select your chart, select the Macro button again, and run that macro you just created. And violà, your labels show up, and you can go about styling the gridlines, etc.
I hope this is helpful and if you have questions, comments, or more ideas, please share them below.