Author: Oscar Cronquist Article last updated on May 17, 2018

The picture above shows a chart that has custom data labels, they are linked to specific cell values.

This means that you can build a dynamic chart and automatically change the labels depending on what is shown in the chart.

I have demonstrated how to build dynamic data labels in a previous article if you are interested in using those in a chart.

In a post from March 2013 I demonstrated how to create Custom data labels in a chart.

Unfortunately, that technique worked only for bar and column charts.

You can't apply the same technique for an x y scatter chart, as far as I know.

Luckily the people at Microsoft have heard our prayers.

They have implemented a feature into Excel 2013 that allows you to assign a cell to a chart data point label a, in an x y scatter chart.

I will demonstrate how to do this for Excel 2013 and later versions and a workaround for earlier versions in this article.

How to apply custom data labels in Excel 2013 and later versions

This example chart shows the distance between the planets in our solar system, in an x y scatter chart.

x y scatter chart - custom data labels3

The first 3 steps tells you how to build a scatter chart.

  1. Select cell range B3:C11
  2. Go to tab "Insert"
  3. Click the "scatter" button
  4. Right click on a chart dot and left click on "Add Data Labels"
  5. Right click on a dot again and left click "Format Data Labels"
  6. A new window appears to the right, deselect X and Y Value.
    Format data labels excel 2013
  7. Enable "Value from cells"
    data label range excel 2013
  8. Select cell range D3:D11
  9. Click OK

This is what the chart shows, as you can see you need to manually rearrange the data labels and add data label shapes.

x y scatter chart excel 365

Back to top

Video

This following video shows you how to add data labels in an X Y Scatter Chart [Excel 2013 and later versions].

Back to top

How to add data label shapes

  1. Right-click on a data label.
  2. Click on "Change data label shapes".
  3. Select a shape.

Back to top

How to rearrange data labels

You can manually click and drag data labels as needed. You can also let excel change the position of all data labels, choose between center, left, right, above and below.

  1. Right click on a data label
  2. Click "Format Data Labels"
    Change label position excel 13
  3. Select a new label position.

Back to top

Download excel 2013 *.xlsx file

Custom data labels in a x y scatter chartv2.xlsx

Back to top

Workaround for earlier Excel versions

This workaround is for Excel 2010 and 2007, it is best for a small number of chart data points.

  1. Click twice on a label to select it.
  2. Click in formula bar.
  3. Type =
  4. Use your mouse to click on a cell that contains the value you want to use.
  5. The formula bar changes to perhaps =Sheet1!$D$3
  6. Repeat step 1 to 5 with remaining data labels.

Change the value in cell D3 and see how the data label on the chart instantly changes.

The following animated picture demonstrates how to link a cell value to a specific chart data point.

Add custom data labels in a x y scatter chart1

If your chart has many data points this method becomes quickly tedious and time-consuming.

I have automated these steps for you in a macro that you can read about below, there is also an example workbook that you can download.

Back to top

Apply custom data labels (VBA Macro)

This macro is made for Excel 2010 but it might work in Excel 2007 as well.

When you run the macro it automatically adds a cell reference to each chart label.

The value in the cell is then linked to the label.

If you change the value in the cell the label value changes as well.

I will explain how to use this macro in greater detail later in this article.

Sub AddDataLabels()
On Error Resume Next
Set Rng = Application.InputBox(prompt:="Select cells to link" _
, Title:="Select data label values", Default:=ActiveCell.Address, Type:=8)
On Error GoTo 0

With ActiveChart
    For Each ChSer In .SeriesCollection
        Set SerPo = ChSer.Points
        j = SerPo.Count
        For i = 1 To j
            SerPo(i).ApplyDataLabels Type:=xlShowValue
            SerPo(i).DataLabel.Formula = "=" & ActiveSheet.Name _
            & "!" & Rng.Cells(i).Address(ReferenceStyle:=xlR1C1)
        Next
    Next
End With
End Sub

Back to top

Where to put the code?

  1. Copy macro (CTRL + c)
  2. Go to the VB Editor (Alt + F11)
  3. Click "Insert" on the top menu.
  4. Click "Module" to insert a code module to your workbook.
  5. Paste code to the module. (CTRL + v)
  6. Return to Excel.
  7. Save your workbook as a macro-enabled workbook (*.xlsm file).
    If you don't the macro will be gone the next time you open the same workbook.

Back to top

How to use macro

  1. Select the x y scatter chart.
  2. Press Alt+F8 to view a list of macros available.
  3. Select "AddDataLabels".
  4. Click "Run" button.
  5. Select the custom data labels you want to assign to your chart.
    Make sure you select as many cells as there are data points in your chart.
  6. Click OK button.

Back to top

Download Excel *.xlsm file

Link data labels to data points in an x y scatter chart.xlsm

Back to top

Recommended articles

How to add horizontal line to chart

This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types of […]

Add pictures to a chart axis

This picture below shows you a column chart with pictures (flags) below each column. Watch this video to learn how […]

How to create a dynamic chart

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]

Dynamic Gantt charts

Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]

Format fill color on a column chart based on cell color

Table of contents Format fill color on a column chart based on cell color Change stacked bar colors Mark Graveson […]