how to do it in Excel: adding data labels

Today’s post is a tactical one for folks creating visuals in Excel: how to embed labels for your data series in your graphs, instead of relying on default Excel legends.

To illustrate, let’s look at an example from storytelling with data: Let’s Practice!. The graph below shows demand and capacity (in project hours) over time.

 
line chart example.png
 

There are a few different techniques we could use to create labels that look like this.

Option 1: The “brute force” technique

The data labels for the two lines are not, technically, “data labels” at all. A text box was added to this graph, and then the numbers and category labels were simply typed in manually. This is what we affectionately refer to as “brute-forcing” your tool to make it look the way you want it to, regardless of its defaults. Remember: your audience only sees the end result of your work, even if the behind-the-scenes steps aren’t exactly elegant. 

 
line chart example.png
 

One benefit of this approach is that I have greater control over the formatting: size, position, and color of the labels. I can easily make them appear how I want them to appear by simply adjusting the formatting, which is much easier to do with a text box than with a genuine data label. The downside is that this method may not scale easily with many graphs, or those that will be frequently updated with new data—as the data changes, the text labels won’t move with them.

Option 2: Embedding labels directly

Let’s look now at an alternative approach: embedding the labels directly. You can download the corresponding Excel file to follow along with these steps: 

Right-click on a point and choose Add Data Label. You can choose any point to add a label—I’m strategically choosing the endpoint because that’s where a label would best align with my design. 

 
line chart in Excel.png
 

Excel defaults to labeling the numeric value, as shown below.  

 
line chart in Excel.png
 

Now let’s adjust the formatting. Click the label (not the data point, but the label itself) twice, so that these white boxes appear around it:

 
line chart in Excel.png
 

Right-click and choose Format Data Label:

 
line chart in Excel.png
 

In the Label Options menu that appears, you can choose to add or remove fields by checking (or unchecking) the corresponding box under Label Contains. To add the word “Demand”, I’ll check the Series Name box.

 
line chart in Excel.png
 

The result is this:

 
line chart in Excel
 

The label appeared in all-caps (“DEMAND”) because it’s referencing the underlying data—I could adjust the header in Column M to “Demand” if I didn’t want the entire word capitalized (this is a stylistic choice).

 
Picture9.png
 

To adjust the number formatting, navigate back to the Format Data Label menu and scroll to the Number section at the bottom. I’ll choose Number in the Category drop-down and change Decimal places to 0 (side note: checking the Linked to source box is a good option if you want the labels to reformat when the formatting of the underlying source data changes).

 
Picture10.png
 

My resulting visual looks like this:  

 
line chart in Excel.png
 

From here, I can manually adjust the label alignment by highlighting the graph and making the Plot area smaller so that the label doesn’t overlap the line:

 
line chart in Excel.png
 

I’ll repeat the same steps to add the Capacity label:

 
line chart in Excel.png
 

The final thing I’ll do is clean up the formatting of those labels—move the numbers in front of the words, change the number format to be rounded to the thousands place, switch the colors of the labels to match the lines they refer to, and make the font for “24K Capacity” bold.

 
demand-vs-capacity-final-labels.png
 

The benefit of this embedded approach is that as my underlying source data changes, the labels will update accordingly. For graphs that will be refreshed frequently, setting up these steps once will save you the headache of searching for and manually manipulating text boxes.

More Excel how-to’s 

For more tactical instructions, check out these previous posts (and let us know in the comments if there’s something you’d like to see): 


This post was inspired by a recent conversation during our bi-weekly office hour sessions. Do you ever need quick input on a graph or slide, or wish you could pick the SWD team’s brain on a project? Subscribe to premium membership for personalized support and get your questions answered. Our team has enjoyed getting to know many of you during these fun and interactive sessions!