Solved

Excel charting: Data-labels on X-Y charts

Posted on 2000-04-26
19
478 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 200 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

749 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