Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations waross on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel X-Y Scatter Plots and Labeling 8

Status
Not open for further replies.
Rockjoint

Rockjoint

Geotechnical
Feb 5, 2002
53
0
0
US
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
 
Replies continue below

Recommended for you

Sort by date Sort by votes
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.

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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...


(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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BhagyeshVashi
  • Locked
  • Question
Need a rename macro for excel
Replies
11
Views
52
sdz
sdz
Kenja824
  • Locked
  • Question
excel Formula Find help
Replies
5
Views
46
Kenja824
Kenja824
Tuckabag
  • Locked
  • Question
Acessing CMD shell in Excel/VBA with keystrokes and copying resultant data 1
Replies
8
Views
62
3DDave
3DDave
jhn1611
  • Locked
  • Question
Compass question excel 4
2 3 4
Replies
62
Views
119
IDS
IDS
3DDave
  • Locked
  • Question
Python now integrated into Excel - apparently
Replies
8
Views
48
IDS
IDS
Back
Top