Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel charting: Data-labels on X-Y charts

Posted on 2000-04-26
19
Medium Priority
?
490 Views
Last Modified: 2008-03-04
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
0
Comment
Question by:mrjlal
  • 14
  • 3
  • 2
19 Comments
 
LVL 13

Expert Comment

by:cri
ID: 2753241
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
 
LVL 13

Expert Comment

by:cri
ID: 2753251
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
 
LVL 13

Expert Comment

by:cri
ID: 2753288
This search engine in E-E is @#°!?. There were at least 3-4 questions in the last year.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:mrjlal
ID: 2753401
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
 
LVL 13

Expert Comment

by:cri
ID: 2753422
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
 
LVL 13

Expert Comment

by:cri
ID: 2753425
d) ... one number at a time on the chart, right click (FormatDataLabel), then Number, Custom and write "Mary" (with quotes)
0
 
LVL 13

Expert Comment

by:cri
ID: 2753494
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
 

Author Comment

by:mrjlal
ID: 2753916
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
 
LVL 13

Expert Comment

by:cri
ID: 2754220
E-E should index (at least) the text of question and what was accepted as answer, not only the title.
0
 
LVL 13

Expert Comment

by:cri
ID: 2754440
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
 
LVL 13

Expert Comment

by:cri
ID: 2775293
Sorry to insist, I would hate to see this one autodeleted, this question comes often. Do you need further assistance ?
0
 
LVL 6

Expert Comment

by:bkpchs237
ID: 2782786
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
 
LVL 13

Expert Comment

by:cri
ID: 2782802
Sigh...

bkpchs237, please read the question (XY-Chart specified) and the thread (your solution #1 is already there).
0
 
LVL 13

Accepted Solution

by:
cri earned 800 total points
ID: 2782805
mrjlal, upgrading to answer. If you need further assistance regarding the four alternatives, please ask.
0
 
LVL 6

Expert Comment

by:bkpchs237
ID: 2782843
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
 
LVL 13

Expert Comment

by:cri
ID: 2783763
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
 
LVL 13

Expert Comment

by:cri
ID: 2783767
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
 
LVL 6

Expert Comment

by:bkpchs237
ID: 2790421
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
 
LVL 13

Expert Comment

by:cri
ID: 2791156
bkpchs237, I apologize for being a bit terse. Download the a.m. tool. It is really handy.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question