Excel X-Y Scatter Plots and Labeling 8

[Rockjoint]

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 Rockjoint
rockjoint@yahoo.com

 

[ivymike]

I must not understand the question properly...

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.

 

[GregLocock]

I think I know what he's trying to do - add a data label (they were called in 123 or quattro) to each point. ie a little text box linked to each x,y point with a description of it. Typically you'd then have 3 columns of data, x, y, and datalabels.

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

 

[ivymike]

Well Greg, if you're right about what he wants, I'm probably not going to be much help...

 

[ivymike]

Scratch that. I started to think about how to write a macro to do the task you mentioned, and then decided that there must be one out there already...

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, ",&quot, xVals, _
Mid(Left(xVals, InStr(xVals, "!&quot - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!&quot, xVals, ",&quot - 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

 

[WireoMesh]

This Excel add-in may be useful too.

http://www.appspro.com/utilities/Labeler.asp

 

[pmover]

Gentlemen,
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

 

[Rockjoint]

I really appreciate everyone's response to my post. And you have given me lots to consider for future spreadsheet work.

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

 

[pmover]

Rockjoint,
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

 

[Rockjoint]

Pmover,

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

 

[GregLocock]

Agreed. I can get pmover's custom labels for a single point to work (neat), but have still failed dismally with getting a whole column of data labels to stick to an XY graph. Cheers

Greg Locock

 

[pmover]

Rockjoint,
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

 

[Rockjoint]

Pmover, Greg, WireoMesh, Ivymike, THANKS for the input. It is really nice having a great group of experienced professionals to call upon.

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

 

[meintsi]

Excel 2002 incorporates this functionallity into a data point labeling option. - Nice option to have.....

 

[terrym]

I have found this XY chart labeller add-in for MS Excel by Rob Bovey to be excellent at getting around the labeling problem discussed above.

It can be found at

http://www.xl-logic.com/pages/addins.html

I hope this helps

Terry Morgan

 

[Rockjoint]

Thanks, Terry, for the assistance. I will check out the site.
Rockjoint
rockjoint@yahoo.com

 

[bigun]

Try

http://www.appspro.com

for a very slick free utility to perform this very helpful function.

 

I have been trying to label my points for plotting price-earnings ratio vs price(as % of 52 week range)of stocks on scatter plots. The Appspro labeler does this task neatly.

try the link

http://www.appspro.com/utilities/utilities.asp

 

[Speedy]

You could take this approach.

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