×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel X-Y Scatter Plots and Labeling
8

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

Rockjoint
rockjoint@yahoo.com

RE: Excel X-Y Scatter Plots and Labeling

2
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.

RE: Excel X-Y Scatter Plots and Labeling

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

RE: Excel X-Y Scatter Plots and Labeling

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

RE: Excel X-Y Scatter Plots and Labeling

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, ","), 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

2
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

RE: Excel X-Y Scatter Plots and Labeling

(OP)
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

RE: Excel X-Y Scatter Plots and Labeling

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

RE: Excel X-Y Scatter Plots and Labeling

(OP)
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

RE: Excel X-Y Scatter Plots and Labeling

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

RE: Excel X-Y Scatter Plots and Labeling

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

RE: Excel X-Y Scatter Plots and Labeling

(OP)
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

RE: Excel X-Y Scatter Plots and Labeling

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

RE: Excel X-Y Scatter Plots and Labeling

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

RE: Excel X-Y Scatter Plots and Labeling

(OP)
Thanks, Terry, for the assistance. I will check out the site.

Rockjoint
rockjoint@yahoo.com

RE: Excel X-Y Scatter Plots and Labeling

Try www.appspro.com for a very slick free utility to perform this very helpful function.

RE: Excel X-Y Scatter Plots and Labeling

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

RE: Excel X-Y Scatter Plots and Labeling

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close