Solved

Excel charting: Data-labels on X-Y charts

Posted on 2000-04-26
19
474 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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 …

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now