Excel Charts: Dynamic Line Series Labels (2024)

Create Dynamic Series Labels in Microsoft Excel

Below shows an example of this: Instead of showing your readers the default chart legend youshould show this – notice the series labels are sitting right by the series:

I’m not saying you should do this ALL the time. I am saying you should do this whenever you think it will make your charts easier to read. Best is to put yourself in your readers shoes. In this case, you’ll be needing the default legend:

For this one however, you will want to position the legend close to the series – The default legend is not as intuitive and easy to read as the perfectly positioned one:

In this article I will show you how to do this for theline series below. If you prefer to watch, you can click on the video above. The challenge is to have the label for the Actual series move with the line series as data for the next months are added. So in the screenshot below, as I add data for July, I’d like my “Actual” label should move with.

Obviously, you don’t want to manually add text boxes to your chart and move them every time your series shifts. As always, we will let Excel do the work for us.

The trick to do this automatically is going to be a trick that you can use for many other purposes and I’m sure you’re going to need it in some of your reports.

To follow along you canDownload the Workbook HERE.

Here are the steps you need to take to create dynamic series labels for your line series

Getting the Budget Label is easy. The position of our budget label is going be our December month. The value we have for December is where we want the label to be. We can add a correction factor to this cell if wefindthat the positioning is not perfect.

Here is our original data table which includes our data preparation columns for the positioning of the labels:

For the Actual column, I could manually set up a formula to get the values from the last entry in the Actual Series column every time I have new data, and delete the previouscell as another month’s data becomes available.

Say the latest data is for September, I would use=B30to capture that value under the Actual column. Once I have new data for the month of October, I would then delete the previous cell in the Actual column, move on to the next cell and write down the formula=B31, and so on. But obviously we don’t want to do it manually.

Excel Line Series for Actual and Budget Data

Before we see what formula we can use for the Actual column, let us go ahead and insert a line series. Highlight the entire table, selectInsert > Line > 2-D Linefrom the ribbon at the top. Change the format by clicking on the chart and thenFormat > Shape Outlineand choose your colors for the Actual and Budget Series.

Now you will have two other series added, the Actual and the Budget. But you can’t see them because they’re just one data point.

Show the Label Instead of the Value for Budget

To see the label for the Budget series, perform the following:

• Select your chart and go to theFormattab, click on the drop-down menu at the upper left-hand portion and select Series “Budget”.
• Go toLayouttab, selectData Labels > Right.
• Right mouse click on the data label displayed on the chart. SelectFormat Data Labels.
• Under theLabel Options,showtheSeries NameandunticktheValue.

Show the Label Instead of the Value forActual

For the Actual column, we want a formula that’s always going to give us the number for the last filled month. I will need anIFformula because I need to check if the cell under the Actual Series column in the same row has a value in itANDthe cell right below it has no number. If both of these conditions are met, then it should give me the number that is sitting in the last filled cell.

I need theIFand theANDfunction. So let’s write in cell D22:

=IF(AND(B22<>""

which means that the cell should not equal to nothing (“”), i.e. it should be filled. The other condition is that the cell right below it should actually equal nothing.So our formula becomes:

=IF(AND(B22<>"",B23="")

If the statement is true, it should give the value of the cell in the same row, otherwise, it should return nothing. We can complete our formula to be:

=IF(AND(B22<>"",B23=""),B22,"")

This looks good, the problem is that if you take a look at the chart, you have a line that goes down to zero.

You canright clickon the data series, and selectFormat Data Series, underLine Colortab, selectNo line.

Now we can do the same steps as we did to get the Budget label.

• Select your chart and go to theFormattab, click on the drop-down menu at the upper left-hand portion and select Series “Actual”.
• Go toLayouttab, selectData Labels>Right.
• Right mouseclick on the data label displayed on the chart. SelectFormat Data Labels.
• Under theLabel Options, showtheSeries NameandunticktheValue.

You can see that now it’s showing the data labels also for zero values. So why does Excel do that?

It’s because I’m using a formula for the Actual column. So even if I say “give me nothing” in the formula, “nothing” for Excel is a zero. And that’s why it assumes these are all zero values. When I activate the data labels, it also provides the data labels for my zero values as well.

If you delete the formulas under the Actual column, the data labels on the chart disappear. But obviously that’s not what we want to do. So writing “” doesn’t work and zeros are not going to work either.

What works is to use “NA” instead of “”. The formula becomes:

=IF(AND(B22<>"", B23=""), B22, NA())

NA() means “give me an error in the cell”. Chart series can’t handle this error. The series is cut-off.

If I copy this formula all the way down to the last row, the chart labels for the zero values disappear and you have no line. You just have this one point because it doesn’t know what to do with the other errors.

If I add the data for the next month, let’s say 2,000 for September, my line series moves up and so does my label. Everything works like a charm.

Use the NA() Function to Avoid a Crashing Line Series

This is a great trick to use when you also don’t want to show your full year or you don’t have the data for your full year and you’re using a formula in your cells. What you get is a crash in your line series all the way down to zero. If you want to avoid that, instead of writing “”, write NA().

I often use this trickwhen I createline series charts for which I have formulas in the data table and Idon’t want to show future periods. I can avoid crashing lines when I use the NA() function.