Excel X-Y Scatter Plots and Labeling
Excel X-Y Scatter Plots and Labeling
(OP)
I am plotting values of Sodium (Y-axis) against values of Calcium (X-axis) in a X-Y Scatter Plot in Excel (no trendline).
I wish to apply a "label" or identification for each of the two sources of data (i.e., the ID of each well from which the two data sources were collected). I am unable, however, to do this, and I do not think Excel is capable of doing this for X-Y Scatter plots. I welcome any comments or guidance that may be available.
Thank you.
Rockjoint
I wish to apply a "label" or identification for each of the two sources of data (i.e., the ID of each well from which the two data sources were collected). I am unable, however, to do this, and I do not think Excel is capable of doing this for X-Y Scatter plots. I welcome any comments or guidance that may be available.
Thank you.
Rockjoint
Rockjoint
rockjoint@yahoo.com
RE: Excel X-Y Scatter Plots and Labeling
Do you want to simply put a series legend on the plot saying "well 1" next to one line (or series of points) and "well 2" next to the other?
Pick your chart with the mouse
right click
pick Source Data from the menu
click the series tab
type the label for each series of points in the "Name" box
click ok
If that's not what you're trying to do, post again with more details.
Alternatively, if you email me the spreadsheet @ ivymike1031@yahoo.com I'll take a look at it and see if I can figure out how to do what you're asking to do. It'd help if you put text boxes on the chart to "mock-up" the labels that you want.
RE: Excel X-Y Scatter Plots and Labeling
Well Isaac, I hope you find a solution and post it, because I've been after that for a long long time.
Cheers
Greg Locock
RE: Excel X-Y Scatter Plots and Labeling
RE: Excel X-Y Scatter Plots and Labeling
http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q213750
(here's the sample code from that article)
Sub AttachLabelsToPoints()
'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String
' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False
'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula
'Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop
'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter
End Sub
RE: Excel X-Y Scatter Plots and Labeling
http://www.appspro.com/utilities/Labeler.asp
RE: Excel X-Y Scatter Plots and Labeling
If I understand the question correctly, there is a method of adding labels to each data point within a series. The data point label can either be the value or label.
To use a custom label (refering to a cell):
1) select the series within the x-y scatter chart,
2) carefully select a data point (you should see a single -sometimes double - solid black square highlighting selected data point,
3) right click the selected (single) data point and choose "Format Data Point...",
4) select one of the options, but not "None",
5) Select "OK"
6) Now select the actual number (or label) being displayed. You should see double solid squares on each end of the data value.
7) select the label again (between the two squares) - you should see a highlighted square, similar to selecting objects within ms products.
8) within the formula bar, enter the equal sign and then select the cell that has the label of choice.
9) press the "Enter" key.
The label can also be orientated to suit desired needs.
I prefer this method to using chart labels, since all that is necessary is to change the cell value. The chart is then automatically updated.
I trust this resolves the request.
Good question and I have been doing this technique since the early qpro days.
-pmover
RE: Excel X-Y Scatter Plots and Labeling
As I stated, I am plotting X-Y Scatter plots. As an example, I want to plot the Calcium and Sodium values as shown below. That is no problem. However, I am unable to apply the "label" or "ID" of each of the X-Y pairs to the data on the scatter plot.
1998 2001
calcium sodium calcium sodium
GW-1D 35.2 10.1 GW-1D 46.1 10.3
NS-6D 37.3 10.85 GW-2DA 74.6 12.43
GW-2DA 44.9 12.48 NS-6D 46.7 12.5
GW-8D 17.68 12.63 GW-8D 28.28 13.53
GW-10D 63.55 24.58 NS-1D 110.1 26.6
NS-1D 103.8 25.6 NS-5D 319.2 30.1
NS-5D 250.2 39.7 GW-10D 85.85 34.78
NS-2D 81.98 43.1 NS-2D 84.6 42.4
NS-4D 126.1 48.4 NS-3D 103.1 71.8
NS-3D 85.5 74.6 NS-4D 342 89.3
I am unable to get the labels to display, regardless of what I try. I am not familiar with script or macros, so that is something I can learn for future use.
Thanks to everyone again for the great assistance.
Rockjoint
Rockjoint
rockjoint@yahoo.com
RE: Excel X-Y Scatter Plots and Labeling
Apply the KISS Principle - keep it simple stupid (not implying you are stupid).
Each data point can display a label.
For custom labels, see my previous response.
For standard features within xl, follow these procedures:
1) right-click select the series on the chart,
2) select format data series...
3) select the tab labeled "data labels",
4) select one of three choices for displaying labels.
5) select ok.
The labels should be shown.
It seems that a column chart would best fit the task.
good luck -pmover
RE: Excel X-Y Scatter Plots and Labeling
I tried once again your last suggestion, and I am unable to get the labels to display. Rather, the values of the data are displayed with the data point.
Referring to my previous post, as an example I wish to have the "GW-1D" displayed when the Calcium value (35.2) is plotted on the X-axis and the Sodium value (10.1) is plotted on the Y-axis. I can not display the GW-1D as a label with the data point, only as an entry in the Legend for the chart.
I do not believe there is a simple way for Excel to do what I want when using X-Y scatter plots. This chart type is often used in geochemical data plotting, and that is why it is being used here. Otherwise, another chart type may be more appropriate.
Thank to everyone once again for the helpful comments.
Rockjoint
rockjoint@yahoo.com
RE: Excel X-Y Scatter Plots and Labeling
Cheers
Greg Locock
RE: Excel X-Y Scatter Plots and Labeling
I believe I've accomplished what you've requested.
Summarizing, x-axis data are calcium and y-axis data are sodium for both years (1998 and 2001). A comparason of both years is desired. There are two series (years) for the x-y scatter chart.
When labels (gw-10d, gw-1d, etc.) are added, there is so much text overlap, it is difficult distinguish between the labels. Each individual label can be orientated to help out, but that is a lot of work.
The labels were added for each data point as outlined in my first posting. For this type chart, I do not recollect an xl built-in technique to add the labels as desired. I will conduct a little research this evening and advise.
I will be glad to send this workbook to you; however, I believe others may be interested as well. I am not familiar with posting the workbook into this forum for all to learn from. If anyone has suggestions, please advise.
Understand the frustration, but persistence does have dividends. I trust this helps.
Greg, thanks! and you are welcome. you ought to experiment a little with custom labels. it makes matter really simple, without having to write code, when labels need to be updated automatically.
-pmover
RE: Excel X-Y Scatter Plots and Labeling
Pmover, I would welcome the workbook if you are able to forward it. My email address is provided below.
Thanks for the suggestions and for helping me with this problem. I hope I can be of assistance to everyone in the future.
Sincerely,
Rockjoint
rockjoint@yahoo.com
RE: Excel X-Y Scatter Plots and Labeling
RE: Excel X-Y Scatter Plots and Labeling
It can be found at http://www.xl-logic.com/pages/addins.html
I hope this helps
Terry Morgan
RE: Excel X-Y Scatter Plots and Labeling
Rockjoint
rockjoint@yahoo.com
RE: Excel X-Y Scatter Plots and Labeling
RE: Excel X-Y Scatter Plots and Labeling
try the link http://www.appspro.com/utilities/utilities.asp
RE: Excel X-Y Scatter Plots and Labeling
I've tried it and it works, but it is very very slow and tedious;
-Graph as normal as 1 series
-Then, /Format Data Series/Data Labels/
Go to the graph and click on the actual label. It will allow you to change the text on the label. This will have to be done for every pt.
Very slow, I know, but if you are desperate.
Speedy