Create Dynamic Series Labels in Microsoft Excel
Below shows an example of this: Instead of showing your readers the default chart legend you should 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 the line 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 can Download the Workbook HERE.
Featured Course
Black Belt Excel Package
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 we find that 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 previous cell as another month’s data becomes available.
Say the latest data is for September, I would use =B30 to 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, select Insert > Line > 2-D Line from the ribbon at the top. Change the format by clicking on the chart and then Format > Shape Outline and 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 the Format tab, click on the drop-down menu at the upper left-hand portion and select Series “Budget”.
- Go to Layout tab, select Data Labels > Right.
- Right mouse click on the data label displayed on the chart. Select Format Data Labels.
- Under the Label Options, show the Series Name and untick the Value.
Show the Label Instead of the Value for Actual
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 an IF formula because I need to check if the cell under the Actual Series column in the same row has a value in it AND the 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 the IF and the AND function. 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 can right click on the data series, and select Format Data Series, under Line Color tab, select No line.
Now we can do the same steps as we did to get the Budget label.
- Select your chart and go to the Format tab, click on the drop-down menu at the upper left-hand portion and select Series “Actual”.
- Go to Layout tab, select Data Labels > Right.
- Right mouse click on the data label displayed on the chart. Select Format Data Labels.
- Under the Label Options, show the Series Name and untick the Value.
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 trick when I create line series charts for which I have formulas in the data table and I don’t want to show future periods. I can avoid crashing lines when I use the NA() function.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
