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.
This means that you can build a dynamic chart and automatically change the labels depending on what is shown on 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.
What's on this page
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.
1.1 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.
The first 3 steps tell you how to build a scatter chart.
- Select cell range B3:C11
- Go to tab "Insert"
- Press with left mouse button on the "scatter" button
- Press with right mouse button on on a chart dot and press with left mouse button on on "Add Data Labels"
- Press with right mouse button on on any dot again and press with left mouse button on "Format Data Labels"
- A new window appears to the right, deselect X and Y Value.
- Enable "Value from cells"
- Select cell range D3:D11
- Press with left mouse button on OK
This is what the chart shows, as you can see you need to manually rearrange the data labels and add data label shapes.
1.1 Video
The following video shows you how to add data labels in an X Y Scatter Chart [Excel 2013 and later versions].
Learn more
Axis | Chart Area | Chart Title | Axis Titles | Axis lines | Chart Legend | Tick Marks | Plot Area | Data Series | Data Labels | Gridlines
1.2 How to add data label shapes
Press with right mouse button on on a data label.- Press with mouse on "Change data label shapes".
- Select a shape.
1.3 How to change data label locations
You can manually press with left mouse button on 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.
- Press with right mouse button on on a data label
- Press with left mouse button on "Format Data Labels"
- Select a new label position.
Learn more
Secondary Axis | Linear trendline | Logarithmic Trendline | Moving Average | Error Bars
2. Workaround for earlier Excel versions
This workaround is for Excel 2010 and 2007, it is best for a small number of chart data points.
- Press with left mouse button on twice on a label to select it.
- Press with left mouse button on in formula bar.
- Type =
- Use your mouse to press with left mouse button on a cell that contains the value you want to use.
- The formula bar changes to perhaps =Sheet1!$D$3
- 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.
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 get.
Learn more
Column | Bar | Line | Area | Pie | Doughnut | Scatter | Bubble | Funnel | Stock | Candlestick | Surface | Radar | Map
2.1 Apply custom data labels (VBA Macro)
This macro adds a cell reference to each data label, the value in the referenced cell is then linked to the label. If you change the value in the cell the label value changes as well.
'Name macro
Sub AddDataLabels()
'Enable error handling
On Error Resume Next
'Display an inputbox and ask the user for a cell range
Set Rng = Application.InputBox(prompt:="Select cells to link" _
, Title:="Select data label values", Default:=ActiveCell.Address, Type:=8)
'Disable error handling
On Error GoTo 0
With ActiveChart
'Iterate through each series in chart
For Each ChSer In .SeriesCollection
'Save chart point to object SerPo
Set SerPo = ChSer.Points
'Save the number of points in chart to variable j
j = SerPo.Count
'Iterate though each point in current series
For i = 1 To j
'Enable data label for current chart point
SerPo(i).ApplyDataLabels Type:=xlShowValue
'Save cell reference to chart point
SerPo(i).DataLabel.Formula = "=" & ActiveSheet.Name _
& "!" & Rng.Cells(i).Address(ReferenceStyle:=xlR1C1)
Next
Next
End With
End Sub
Learn more
Waterfall | Treemap | Sunburst | Histogram | Pareto | Box & Whisker
2.2 Where to put the code?
- Copy macro (CTRL + c)
- Go to the VB Editor (Alt + F11)
- Press with left mouse button on "Insert" on the top menu.
- Press with left mouse button on "Module" to insert a code module to your workbook.
- Paste code to the module. (CTRL + v)
- Return to Excel.
- 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.
Learn more
Arrow | Normal distribution | Equation | Comparison | Heat map | Gantt
2.3 How to use macro
- Select the x y scatter chart.
- Press Alt+F8 to view a list of macros available.
- Select "AddDataLabels".
- Press with left mouse button on "Run" button.
- 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.
- Press with left mouse button on OK button.
Recommended articles
Recommended articles
This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types […]
Recommended articles
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
Recommended articles
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
Recommended articles
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
Recommended articles
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
Charts category
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]
You can easily change data labels in a chart. Select a single data label and enter a reference to a […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
Scatter x y chart category
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
The picture above shows the following equation x^3+3*x^2-3 plotted on an x y scatter chart. Here are the instructions on how […]
This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types […]
The chart above is built using the NORM.DIST function and is called Normal Distribution or Bell Curve chart. This curve is often […]
The scatter chart is great for charting numeric values in pairs, for example, coordinates. It lets you compare multiple data […]
Excel categories
11 Responses to “Improve your X Y Scatter Chart with custom data labels”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Thank you very much! This and so many other basic aspects of something as basic to anyone who crunches numbers are so arcane in Excel, I was thinking I'd have to write some C# to do this. Thanks Oscar!
This does not appear to work, at least not with Excel for Mac. "Value From Cells" is not an option listed in the Data Label options. The only options given are Series Name, X Value, Y Value, and Bubble Size. On a more general note, Microsoft has consistently under-delivered with Office for Mac vs. their Windows edition. Charting options have improved a little but the UX is still woefully inadequate and convoluted.
Thank you for your Excel 2010 workaround for custom data labels in XY scatter charts. It basically works for me until I insert a new row in the worksheet associated with the chart. Doing so breaks the absolute references to data labels after the inserted row and Excel won't let me change the data labels to relative references. Do you know a work around for that?
Brian
Try building a named range.
1. Select the cell range you want to use.
2. Type the name of the named range in the name box (next to the formula bar).
3. Press Enter
4. Use the name in your chart. I believe you need to provide the sheet name as well. =Sheet1!NamedRange
¨Value from cells¨ does not appear in my Format Data Labels sidebar menu :/ Any ideas how to fix that? I would try work arounds but would prefer having the straightforward way available.
Thanks for the great article :)
Lora
Which Excel version do you use?
This doesn't work on the Mac version. I can get to "Format Data Labels", and there are choices for X value, Y value, Series Name, Show Leader Lines, and Legend Key, but not "Value from Cell". How do I do this on mac?
Someone wrote this comment on the Peltier blog:
The thing is that I work on a Mac and, as you already know, the “”Value from Cells” doesn’t exist. I had to open the file on a Windows computer, did the job, re-opened in my Mac again and it worked pretty well.
https://peltiertech.com/apply-custom-data-labels-to-charted-points/#comment-838263
Great!
I have one problem and that is in the script you use for the work around for older excel versions.
In the last step of the for loop:
SerPo(i).DataLabel.Formula = "=" & ActiveSheet.Name _
& "!" & Rng.Cells(i).Address(ReferenceStyle:=xlR1C1)
This only works with DATA on the same sheet as the scatter graph to be used as labels.
I have my DATA on another sheet and not the same active sheet as the scatter graph.
How would you modify this so DATA on a different sheet can be used as data labels?
Thanks!
Thanks for this article! Helped me a lot.
Many thanks Oscar, finding out how to add labels to scatterplots is a game changer. I'm sure I'll be back to learn some of the other chart enhancements!