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?
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
On 4/23/08 2:40 AM, in article ee995...@webcrossing.caR9absDaxw,
"pau...@officeformac.com" <pau...@officeformac.com> wrote:
--
This posting is provided "AS IS" with no warranties, and confers no rights.
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.
> 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.
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.
> > 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.
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.
--
Team EM to the rescue! http://www.team-em.com
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.
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.
paul
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.
--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom
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?
and then, to my point earlier, i cant put any trendline through the
points (bc they're treated as different series)