MS Excel graphing help? Please?

[TUS 373]

Can anyone answer this (probably very simple) question for me please?

I am trying to produce a scattergraph from a table of data. For simplicity, lets say I have 3 columns, A, B & C.

A contains names to values e.g. car names.

Columns B & C contain my X and Y co-ordinates, respectiviely. I can produce the scatter graph using these co-ordinates, no problem at all.

Problem is, I want the co-ordinates to pick up and display their corresponding names from column A. Whenever I try I end up with hundreds of them, and all in the wrong place.

Can anyone explaing how to do this and keep the data linked together properly. The s@dding paperclip Office Assistant is bloody useless in explaining it to me!

Thanks in anticipation,
Bryan

[TUS 373]

Anyone? I'm stuck!
Thanks

[m-five]

1) Select the label column along with the two 'X' and 'Y' columns

2) Create a scatter graph and click finish instead of going through all the questions

3) Go to 'Chart:Chart Options...', select 'Data Labels' tab, check 'Show Label' and press 'OK'

4) Go to 'Chart:Source Data...', select 'Series' tab

5) Select 'Series 1' and press 'Remove'

6) Press 'OK'

Don't know why it works, but it does and it is easier/quicker than trying to find the proper way!

The other option is to do a bubble chart!

[TheGroover]

m-five said:

1) Select the label column along with the two 'X' and 'Y' columns 2) Create a scatter graph and click finish instead of going through all the questions 3) Go to 'Chart:Chart Options...', select 'Data Labels' tab, check 'Show Label' and press 'OK' 4) Go to 'Chart:Source Data...', select 'Series' tab 5) Select 'Series 1' and press 'Remove' 6) Press 'OK' Don't know why it works, but it does and it is easier/quicker than trying to find the proper way! The other option is to do a bubble chart!

I don't think thats going to give TUS what he wants .. Am I right in thinking that if you have A,2,3 for example, you need the dot for A to appear at co-ordinates X2Y3 ?
The method will only give you a dot for A in either position 2 or 3 on the Y axis.
I think (though I may be corrected!) the only way you can get the dot in position X2Y3 is to build the graph without the labels i'm afraid, and then manually add them in.

[TUS 373]

Thanks Guys, I'm still struggling on this. There are 95 plot points - that's alot to label manually. I'll keep trying.

As you rightfully say, if co-ordinate 2,3 is called A, then a want 'A' to appear next to the dot for co-ordinate 2,3 to identify it. Just can't make it work though and its a really frustrating waste of time that I don't have! Aaaarrrggggggggghhhh!!!!!!

[m-five]

Sorry, the numbers I picked just happened to fit the XY coodinates so I didn't se my obvious error.

The actual answer according to Microsoft is:
"In Microsoft Excel, you can automatically attach text labels to data points in an xy (scatter) chart. However, if you want to attach labels that are different from the default labels, you must select the label and manually type a formula or text for the label."

However you can save some time in typing all the labels in by referencing the label to a cell instead - just click on the 1st label and highlight the text, then go to the formula bar above and type in the cell reference instead. At least this will change with any source data change. and should only take 10 min or so for 90 labels!

[TUS 373]

Thanks for your help Tony.

So it looks like there is no quick and automatic way. I'm surprised really as I though that Excel 'does everything'. Obviously not!

I see how I can add the labels in manually now so that is a great help. Thankyou for your attention and reducing my stress levels dramatically (well they will be lower when I have finished doing it anyway!).

[GreenV8S]

Now that you've done it the hard way ...

Maybe there are better ways, but one way to do this is to use a separate series for each point and then select the series name as the label for the point. You can do this manually if you don't mind going through it 95 times, but if you lay the data out in a particular way, the chart wizard can do this automatically:

Leave A1 empty, put your labels in column A, put your X values on row 1, put the Y values at the intersection of the corresponding label and X value. For example:

,1,2,1,2
bottom-left,1,,,
bottom-right,,1,,
top-left,,,2,
top-right,,,,2

This gives the effect you're after without manually defining every data label or manually defining every data series. Shuffling the data itself around to produce this layout would be a bit tedious, but maybe some clever person can find a way to automate that or remove the need for it?

[TJMurphy]

You've probably finished by now (sorry, don't check this forum too regularly) but have a look at Rob Bovey's site. If you look under Free Utilities there's a link to a program called XY Chart Labeller, which if I've understood your question correctly should do what you want.

Tony

[TUS 373]

Thanks Tony, that's really cool. I have labelled the 95 co-ordinates manually, took me a while to do it yesterday. I have downloaded the extra software from your link for future use - as now doubt, I will be needing it again in the future!

How on earth did you find that site, its really useful.

Thanks for your help,
Merry Xmas!
Bryan

[tjmurphy]

You're welcome.

I'm a sad git, most of my work involves using spreadsheets and PCs so I've come across a fair view links connected with it over time. This one actually came from the MVPs list. MVP is a sort of award Microsoft gives for people who help out other people on websites about their software. These guys really know their stuff (far more than me).