how to add data labels in excel

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.

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.

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.

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

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:

Right-click and choose Format Data Label:

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.

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).

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).

My resulting visual looks like this:

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:

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

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.

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!