Grab the complete, ready-to-use chart template now! Click 👉 HERE to Download.

Why Use Dynamic Series Labels in Excel Charts

Creating dynamic line series labels in Excel can make your charts much easier to read. Instead of using the default chart legend, place the labels directly next to the series. This approach enhances readability.

You don’t need to use dynamic labels all the time. Use them when they make your charts clearer. Think about your audience. For some charts, the default legend is fine. For others, dynamic labels are better.

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:

How to Add Dynamic Data Labels in Excel

In this article, I’ll show you how to create dynamic labels for a line series.

Imagine you have a line series called “Actual.” As you add data for new months, you want the “Actual” label to move with the line.

Manually adding and moving text boxes is tedious. Instead, let Excel do the work for you.

Featured Course

Business Charts in Excel

Create Business charts that grab attention AND auto-update. Wow your coworkers and managers with smart time-saving techniques.
Learn More

Step 1: Setting Up the Budget Label

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:

Step 2: Setting Up the Actual Label

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.

Step 3: Inserting a Line Chart

  • Create the Chart: Highlight your data table and select Insert > Line > 2-D Line from the ribbon.
  • Format the Chart: Click the chart, then go to Format > Shape Outline to choose colors for the Actual and Budget series.

Step 4: Adding the Budget Label

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

  • Select the Series: Click on your chart, go to the Format tab, and select Series “Budget” from the drop-down menu.
  • Add Data Labels: Go to the Layout tab, select Data Labels > Right.
  • Format Data Labels: Right-click the data label on the chart, select Format Data Labels, show the Series Name, and uncheck the Value.

    Step 5: Adding the Actual Label

    For the Actual column, we want a formula that’s always going to give us the number for the last filled month.

    • Write the Formula: In cell D22, write:
    =IF(AND(B22<>"", B23=""), B22, NA())

    This formula checks if the cell in the Actual series is filled and the next cell is empty. If true, it returns the value; if not, it returns NA(), which Excel treats as an error.

    • Copy the Formula: Copy this formula down the column. The chart won’t show lines for NA() values.
    • Select the Series: Select Series “Actual” in the chart.
    • Add Data Labels: Go to the Layout tab, select Data Labels > Right.
    • Format Data Labels: Right-click the data label, select Format Data Labels, show the Series Name, and uncheck the Value.

    Why Use NA()?

    Using NA() hides zero-value labels on the chart, as Excel treats NA() as an error, making the series stop at the last data point.

    Adding New Data

    When you add new data, like 2,000 for September, the line and the label for the Actual series will move accordingly. This keeps your chart updated automatically without manual adjustments.

    By following these steps, you can create dynamic series labels that update automatically, making your charts clear and professional.

    Download the Chart Template

    Simplify your work and save time with our ready-to-use chart template. Just download, customize to fit your needs, and instantly improve your Excel charts. Get the complete solution now and see the difference in your reports.

    Featured Course

    Black Belt Excel Package

    What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
    Learn More
    Excel Black Belt course cover

    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.