I recently discussed four options for labeling line graphs. A personal favorite is to place the labels directly on top of the data points, like this:

labeling-line-graphs_f_data-points-centered-on-lines

There’s been a lot of interest in this labeling technique, so here’s your step-by-step tutorial!

 

First, set up your data table:

line-chart-labeling-tutorial-1

Highlight the entire data table (A1 through H3), go to the Insert tab, and click on the line chart icon.

You’ll get a 1990s-era default line chart:

line-chart-labeling-tutorial-2

Do some basic formatting.

Adjust the y-axis so that the grid lines don’t appear quite so often (e.g., every 50 units rather than every 20 units).

Apply a custom color palette (colors that match the organization’s look and feel rather than Excel’s blue and orange).

You might even add light gray tick marks along the x-axis.

line-chart-labeling-tutorial-3

Now let’s add labels.

Click anywhere along the line, just once, and you’ll see little circles on top of each data point:

line-chart-labeling-tutorial-4

Right-click on top of one of those circular data points.

You’ll see a pop-up window.

Click on Add Data Labels.

line-chart-labeling-tutorial-5

Your unformatted labels will appear to the right of each data point:

line-chart-labeling-tutorial-6

Click just once on any of those data labels. You’ll see little squares around each data point.

Then, right-click on any of those data labels. You’ll see a pop-up menu. Select Format Data Labels.

line-chart-labeling-tutorial-8

In the Format Data Labels editing window, adjust the Label Position.

By default the labels appear to the right of each data point.

Click on Center so that the labels appear right on top of each point.

line-chart-labeling-tutorial-9

Umm yeah. So the labels are totally unreadable because they’ve got a line running through them.

Click on the labels again so that a square appears around the outside of the labels. Go to Shape Fill (the paint can icon) and fill each label with a white background.

line-chart-labeling-tutorial-10

Then, do the same thing to the orange line: Add data labels. Adjust the label position so that the labels are centered on top of each data point. Finally, fill the label with white so that the text is legible.

Easy, right?!

Continue formatting a bit more. You might enlarge the label text and make that text bold so that it stands out against the gray axis labels. I often adjust the label colors so that the labels match the line (maroon numbers to match the maroon line, orange numbers to match the orange line).

To add the Organization A and Organization B text, I would *usually* click Format Data Labels and check the box next to Series Name. In this example, that doesn’t quite work; you’ll need to manually add two text boxes.

line-chart-labeling-tutorial-11

 

Want your team to learn these strategies and dozens more? I’m teaching professional development workshops for the Global Environment Facility, Rio Tinto, The Evaluation Group, Dexis Consulting, 21st Century Community Learning Centers, and GrantStation over the next month. Contact me to schedule your organization’s session.