Excel charting: Data-labels on X-Y charts

I would like to use data-labels on an XY Chart in Excel.  Unfortunately, it appears that data-labels, for an XY chart, are restricted to showing the value only, which is pretty worthless to me (I want text to show up).  Does anyone know a work-around for this, or know of any cheap/easy software that will allow me to download my data and chart it with labels?  Thanks,
Mark
mrjlalAsked:
Who is Participating?
 
criCommented:
mrjlal, upgrading to answer. If you need further assistance regarding the four alternatives, please ask.
0
 
criCommented:
You have three options:

a) Manually: Replace label value with text. Contrary to text boxes these will follow any value changes. Good if the diagram is pre-defined but you update the values regularly.

b) Using VBA/Excel to replace the a.m. label values

c) There are add-ins. Will look it up.
0
 
criCommented:
Forgot one:

d) There is a nifty trick from ture: Use custom number format to display anything you want. Of course you can also automate it by VBA.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
criCommented:
This search engine in E-E is @#°!?. There were at least 3-4 questions in the last year.
0
 
mrjlalAuthor Commented:
cri,
Great -- I think we're getting somewhere. I like option "a" for simple stuff, and I'm sure in the future I will use it.  My current problem involves over 100 data points so it's a little cumbersome to do it manually.

My problem is I'm not a VBA person (yet, maybe someday), so "b" is out...waiting for more info on "c"...I didn't understand "d."  Say for instance, I have the following chart (a little sexist, but it was the cutist example I could think of)
Name       Brains       Beauty
Mary         4            6
Jane         2            9
Betty        10           3
Sue          8            9
I want to chart these on an x-y graph and have their names as data-labels.  How do I use custom number format to do that?  

Thanks!
0
 
criCommented:
Expanding:

b) Although you stated VBA is out: Print this article out and follow it, it is not difficult and there are enough people here to help you:
XL: Macro to Add Labels to Points in an XY (Scatter) Chart
http://support.microsoft.com/support/kb/articles/q161/5/13.asp

c) Still searching. What is your hurting limit for an add-in ?

d) Here how to do it manually: Display Data label values. Select one number at a time and put custom number format "Mary". Be sure you do never have all selected, otherwise suddenly all will be named "Sue"...
0
 
criCommented:
d) ... one number at a time on the chart, right click (FormatDataLabel), then Number, Custom and write "Mary" (with quotes)
0
 
criCommented:
c) Sorry, can not find the PAQ. I remember it well, a (probably) french onetimer expert scored with this URL last summer to autumn of last year. Can not find it in the net too. Sorry. Arghhh...

As alternative, at 145$ an overkill for labeling only, but have a look what it can do
Homepage:  www.xlstat.com   (Click "Overview" then link "this page")
Review:  http://www.zdnet.com/downloads/stories/info/0,,000GEB,.html
0
 
mrjlalAuthor Commented:
Thanks cri,
I downloaded the VBA solution article...your right, it looks pretty easy and I welcome the chance to get a little more experience without much headache.  It looks like it pretty well walks you through every click.

I, too, found the search engine to be pretty unhelpful (at least they have one now).  Of course, it would help if people would title their problems something other than "*** HELP!!!! ***"

I'll be back in touch tomorrow after I try it out and get it working.  Thanks!
0
 
criCommented:
E-E should index (at least) the text of question and what was accepted as answer, not only the title.
0
 
criCommented:
c) Found it ! http://archive.baarns.com/excel/free/Exceluts.asp

It works, its free and as source code is not protected, you can see how it is done (although I suggest you start walking with VBA before you go dancing...)
0
 
criCommented:
Sorry to insist, I would hate to see this one autodeleted, this question comes often. Do you need further assistance ?
0
 
bkpchs237Commented:
Using the sample "sexist" data above I created a small chart from cells a1:b5 with your data in two diff ways:

1) Highlight a1:b5, Insert, Chart, Select XY-Scatter, first chart with just points, next, next, data labels, and you should be able to select show value or show labels, yet not both.  If you want to show "the other" you will need to create individual text boxes with either the text within it or link it to the cell values; or

2) What I really think you are looking for here is: Highlight a1:b5, Insert, Chart, Select Line chart, choose "Lines with markers displayed at each data value" option, next, series in columns, next, show value, next, finish.  Then click on the each series (one at a time), Format, Selected Data Series, Line None, OK.

Hope this helps.
0
 
criCommented:
Sigh...

bkpchs237, please read the question (XY-Chart specified) and the thread (your solution #1 is already there).
0
 
bkpchs237Commented:
Not to argue the point back and forth, especially since you are entertaining a solution from another, but I did read the question.  Excel allows one to edit the chart type as I'm sure you already know.  Just because you specified that you wanted to start one way doesn't mean the desired effect can't be accomplished another way.  After all, you did ask for a workaround.  I submit that line chart, with data markers, with no line IS an xy-scatter chart.  If you select show value or show label you will have your desired effect.  Also, the line chart allows you to edit the axis labels, legend, data points, etc. much more easily and effectively than the xy-scatter chart does.
0
 
criCommented:
bkpchs237, if your x values do not start with 0 and the data is not distributed equaly, then a line diagram will not even look like a XY-Diagram.

And should you need a trendline, this is possible in a line diagram, but only on the display, mathematically it will be nonsense unless you introduce a scaling factor to allow x=1,2,3...
0
 
criCommented:
If you want an example:

x   y=x^2
2   4
3   9
4   16
6   36

Make XY chart and a line chart out of this and introduce a power trendline.

 
0
 
bkpchs237Commented:
I wondered why your insistence on an XY-scatter chart on data headers that were non-linear, non-scale and text (non-numeric).  Now this example seems to show a little more of what you really want.  Will reconsider and apologize for wasting your time on the solutions to the previously proposed question, which I see is now altogether different from this one.
0
 
criCommented:
bkpchs237, I apologize for being a bit terse. Download the a.m. tool. It is really handy.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.