Author: Oscar Cronquist Article last updated on February 05, 2018

My fascination for charts and vba continues (see previous posts). This post demonstrates how to highlight a group of values and how to add corresponding data labels. I think the animated picture below says it all.

Dynamic data labels in a chart1

How I created this chart

Add a drop down list to cell I 18

  1. Select cell I18
  2. Go to tab "Data"
  3. Click "Data Validation" button
  4. Select List
  5. Type Africa, Asia, Europe, North America, South America in source:
    data validation dynamic data labels
  6. Click OK

Insert a scatter chart

  1. Select the numbers in table table1
    table - dynamic data labels
  2. Go to tab "Insert"
  3. Click "Scatter" chart button
  4. Click "Scatter with only markers"

Change y and x axis formatting.

VBA

  1. Right click on sheet name
  2. Click on "View Code"
    table - vb editor1
  3. Paste code (see below) to sheet module

    table - vb editor

  4. Exit VB Editor
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Integer
If Target.Address = "$I$18" Then
    With ActiveSheet.ChartObjects(1).Chart
        For r = 1 To Range("Table1[Country]").Rows.Count
            If Range("Table1[Region]").Cells(r).Value = Range("$I$18") Then
                .SeriesCollection(1).Points(r).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
                .SeriesCollection(1).Points(r).ApplyDataLabels
                .SeriesCollection(1).Points(r).DataLabel.Text = Range("Table1[Country]").Cells(r).Value
            Else
                .SeriesCollection(1).Points(r).Format.Fill.ForeColor.RGB = RGB(198, 198, 198)
                On Error Resume Next
                .SeriesCollection(1).Points(r).DataLabel.Delete
                On Error GoTo 0
            End If
        Next r
    End With
End If
End Sub

Download excel *.xlsm file
Dynamic data labels in a chart.xlsm

Related articles

Use a map in an Excel chart

This post describes how to add a map (background picture) to a chart. The following animated picture shows a scatter [โ€ฆ]

Change chart data range using a Drop Down List [VBA]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above [โ€ฆ]

Quickly change chart data source

In excel it is not easy to change a chartยดs data source without manually changing the data source in chart [โ€ฆ]

How to create an interactive Excel chart [VBA]

This article describes how to create an interactive chart, the user may click on a button or multiple buttons and [โ€ฆ]

How to use mouse hover on a worksheet [VBA]

I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option [โ€ฆ]

Improve your X Y Scatter Chart with custom data labels

The picture above shows a chart that has custom data labels, they are linked to specific cell values. What's on [โ€ฆ]