How can I label points in an XY scatter plot

[paulcwr]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

I am trying to do an XY scatter plot and label each point with the name of the point, rather than the X or Y value, which is what happens if i select "add data label". There used to be an add-in called XY chart labeller for O04 but obviously that does not work in 08. Can anyone help?

 

[June Low [MSFT]]

Hi Paul

Once you've added the data labels via "add data labels", you can click on
each label individually and modify the labels to be whatever you like. A bit
of a pain if you have a lot of points to label. Hopefully someone with more
XL expertise will be able to come up with a more efficient solution.


-June
OfficeArt Tester, MacBU
Microsoft

 

[Joshua]

First, I want to say that the lack of an option to easily add custom data point labels is a major PitA for anyone who routinely performs multiple comparison tests (ie Tukey's HSD, Bonferroni correction, Scheffe, etc). This probably includes anyone in the life sciences.

That being said, is their any way to get text boxes to line up in a defines position relative to their data point. What I'm trying to do is get the edited text box to center above or below the associated data point. It may not sound like it matters but when it doesn't line up correctly it's incredibly obvious to me. I know that it should be possible because Keynote does it automatically, however keynote still doesn't allow error bars so I'm forced to use MS Office to generate my charts.

If it's not possible to do this automatically, is there at least a way to force excel into giving me more precision in placing my text boxes. It currently either ignores my slight adjustments, or over compensates and moves the text box much further than I want it to.

I know that there is a similar problem when adjusting column widths in word tables, but by pressing a button (I believe it's the option key) while grabbing the slider you are able to make adjustments of width down to 0.01 inches.

I'm pretty sure I used to be able to select a text box and then by using the arrow keys get it to move in small increments into place, but in Excel '08 the arrow keys change the object of focus instead, which isn't helpful at all.

 

[JE McGimpsey]

First, I want to say that the lack of an option to easily add custom data
point labels is a major PitA for anyone who routinely performs multiple
comparison tests (ie Tukey's HSD, Bonferroni correction, Scheffe, etc). This
probably includes anyone in the life sciences.

Did SP1 fix your problem? If I select a data point on an XY-Scatter
chart, right-click and choose Add Label, I can then edit the label. IF
that's not what you mean, perhaps more detail would help.

That being said, is their any way to get text boxes to line up in a defines
position relative to their data point. What I'm trying to do is get the
edited text box to center above or below the associated data point. It may
not sound like it matters but when it doesn't line up correctly it's
incredibly obvious to me. I know that it should be possible because Keynote
does it automatically, however keynote still doesn't allow error bars so I'm
forced to use MS Office to generate my charts.

In XL, shapes, including ChartObjects (embedded charts), live on the
drawing layer. They can be grouped, but the relative position is not
preserved.

However, if you bring the chart into PowerPoint (which is the direct
analogue of Keynote, rather than XL) you have much more control over
relative positioning.

If it's not possible to do this automatically, is there at least a way to
force excel into giving me more precision in placing my text boxes. It
currently either ignores my slight adjustments, or over compensates and moves
the text box much further than I want it to.

I know that there is a similar problem when adjusting column widths in word
tables, but by pressing a button (I believe it's the option key) while
grabbing the slider you are able to make adjustments of width down to 0.01
inches.

I'm pretty sure I used to be able to select a text box and then by using the
arrow keys get it to move in small increments into place, but in Excel '08
the arrow keys change the object of focus instead, which isn't helpful at
all.

In XL, option->arrow moves text boxes a pixel or so at a time.

 

[Joshua]

Did SP1 fix your problem? If I select a data point on an XY-Scatter

chart, right-click and choose Add Label, I can then edit the label. IF
that's not what you mean, perhaps more detail would help.

I'm trying to make roughly 40 charts with 6 data points per chart. Editing 240 individual text boxes is going to take most of a day. Never mind the fact that I have to spend time changing the formatting from the default (Adding custom error bars, moving the legend, changing the x/y orientation, etc.) for each chart. The option to select a range of cells, similar to what is done for custom error bars is what I'm really looking for.

In XL, shapes, including ChartObjects (embedded charts), live on the
drawing layer. They can be grouped, but the relative position is not
preserved.

However, if you bring the chart into PowerPoint (which is the direct
analogue of Keynote, rather than XL) you have much more control over
relative positioning.

Thank you this may come in handy when doing my final formatting for presentation.

In XL, option->arrow moves text boxes a pixel or so at a time.

This does not work. Option -> arrow to the right cycles through each individual data points text box without moving it and then to the major gridlines. The text boxes I'm using are the labels that I manually edited.

 

[Carl Witthoft]

I'm trying to make roughly 40 charts with 6 data points per chart. Editing
240 individual text boxes is going to take most of a day. Never mind the fact
that I have to spend time changing the formatting from the default (Adding
custom error bars, moving the legend, changing the x/y orientation, etc.) for
each chart. The option to select a range of cells, similar to what is done
for custom error bars is what I'm really looking for.

I suppose it would be hopeless to suggest using GnuPlot or R? They
both allow you to create a namelist as part of a data array, and then
you can easily plot the name.

 

[Joshua]

I suppose it would be hopeless to suggest using GnuPlot or R? They

both allow you to create a namelist as part of a data array, and then
you can easily plot the name.

I'm not really looking to learn a new language. I'm not a programer by training (I know a little perl and mysql and that's it) and don't want to have to learn a new program. The main reason I wanted to use excel is because some times my data changes and I want excel to be able to automatically update my tables. Apparently it's not going to work though because since installing SP1 i get "Not enough memory" error every 10 min. or so, excel runs slower on my MBP than '04 did on an 800 mhz machine, and crashes every 15 min unless I save my progress, quit and restart every time I get the "Not enough memory" error.

 

[Miriam]

Hi, I've had similar problems. In order to reasonably add unique labels to
data points, I found the easiest way was just to code a macro that would
create a new series for each point (don't worry, I've included the code), and
then you can choose to display all data labels as their series names or else
you can choose select points and have only their labels show up. (That
should be pretty straight forward, just right click and format data labels,
make sure you select series name for the labels).

As far as reformatting all the individual plots, you could make your life a
lot simpler if you record a macro to do it for you, especially if all your
plots look the same. Make sure you have the chart in question selected.
Then just go to tools, record new macro, then give it some sweet name and hit
ok. It will record all of your key strokes. Do everything like you would
normally do and then at the end hit stop. The next time that you have a
chart, select it, go to tools, macros, and play your macro. It will take
care of all the formatting for you.

Now, the code for data labels (note this makes all the points blue diamonds.
That shouldn't be too hard to change if you just learn a little visual
basic). First create an xy scatter chart in the same sheet as your data, but
don't add any series (it'll prompt you for those later), just hit finish.
Then run the macro LabelPoints

Sub LabelPoints()
'
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select

Dim myXValues As Range
Dim myYValues As Range
Dim myNameValues As Range

Set myXValues = Application.InputBox(prompt:="Range of X Values?",
Type:=8)
Set myYValues = Application.InputBox(prompt:="Range of Y Values?",
Type:=8)
Set myNameValues = Application.InputBox(prompt:="Range of Labels?",
Type:=8)

For i = 1 To 20
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i).XValues = myXValues(i)
ActiveChart.SeriesCollection(i).Values = myYValues(i)
ActiveChart.SeriesCollection(i).Name = myNameValues(i)

ActiveChart.SeriesCollection(i).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = 25
.MarkerForegroundColorIndex = 25
.MarkerStyle = xlDiamond
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
Next

End Sub

Just put this into a new module in Visual Basic and it should work pretty
well.

 

[Miriam]

Sorry, I forgot, in that For i = 1 To 20 part, change the 20 to however many
data points you're working with. I haven't figured out how to automate that
part yet, maybe someone else would know.

 

[Miriam]

Oh and for those of you who don't know about visual basic, just go to tools,
macros, visual basic editor.

 

[paulcwr]

Miriam, thank you for those fantastic explanations - you clearly are a visual basic expert but the problem is that excel 08 does not support visual basic. if i was using excel 04, i would have used the fantastic xychart labeller add-in which people can download but in 08 it does not work anymore than visual basic. you don't want to do a script by any chance?

paul

 

[Miriam]

I don't know much about Excel 07, I mostly work with 03, but according to the
Microsoft office website, you can access Visual Basic in Excel 07 by clicking
the Developer Tab and then Visual Basic. I do have Excel 07 on a laptop at
home and I'll double check it later tonight to see if the code still applies.

 

[Miriam]

Oh I'm sorry, I didn't realize you were running it on a Mac. Yeah, I can't
help you with that, I'm sorry.

 

[paulcwr]

are you talking about excel for PC or for Mac? i am talking about excel 08 for mac. sorry.

 

[vfxjohn]

are you talking about excel for PC or for Mac? i am talking about excel 08 for mac. sorry.

id just like to re-emphasize the need to add labels to a scatter
plot. ive spent the past 30min trying to figure it out....

 

[Bob Greenblatt]

id just like to re-emphasize the need to add labels to a scatter
plot. ive spent the past 30min trying to figure it out....

Have you looked at the chart formatting section of the formatting palette?
Make the palette visible by selecting it from the tool box icon. Select the
chart, and you should be able to do what you want. If not, let us know what
you tried, and what is not working.

 

[paulcwr]

what we are both trying to do is to label in one fell swoop lots of points on an xy chart, with all of them corresponding to different obsevations. Think of a graph showing GDP per capital and infant mortality rate and trying to show how african countries are on a different pane as asian countries. each point corresponds to a country and is labelled with the name of the country. this is a standard type of graph and i am amazed that excel still does not make it easy to do this. The same problem applies to bubble charts. When visual basis was supported, an add-in called xychart labeller, available at <http://www.appspro.com/Utilities/ChartLabeler.htm> used to do the trick but msft killed that option with its silly (or shall i call economic) decision not to support VB in excel 08.

 

[vfxjohn]

Have you looked at the chart formatting section of the formatting palette?
Make the palette visible by selecting it from the tool box icon. Select the
chart, and you should be able to do what you want. If not, let us know what
you tried, and what is not working.

it doesn't suffice. allowing only value or category names for labels
isn't sufficient. i need to be able to have labels be based on a user-
defined row/column.

yes, i can tel the graph to add labels and then manually change each
one. but that's not efficient when i have 15+ data points. it would
also be nice if each point could be treated as a separate series, but
still allow for a trendline to be drawn across all of them. (though,
that's another issue entirely)

best,
//john

 

[Bob Greenblatt]

it doesn't suffice. allowing only value or category names for labels
isn't sufficient. i need to be able to have labels be based on a user-
defined row/column.

yes, i can tel the graph to add labels and then manually change each
one. but that's not efficient when i have 15+ data points. it would
also be nice if each point could be treated as a separate series, but
still allow for a trendline to be drawn across all of them. (though,
that's another issue entirely)

best,
//john

Have you tried editing the series formula to point to the range of cells
that contains the labels?

 

[vfxjohn]

ive tried doing things all different ways-- including using the
'switch row/column' feature to help with the labeling. the problem
is, i end up with 15+ different series, all using funky variations of
markers. it's just not the same.

and then, to my point earlier, i cant put any trendline through the
points (bc they're treated as different series)