How do I get Excel to produce a property labelled XY scatter plot of my data?

Question

I have data in this format:

Label X-value Y-value
Dog 14 11
Cat 10 13
Horse 22 8
Bunny 9 11

I would like to see an XY scatter plot of this data (2nd, 3rd columns) with each data point labelled with the label name (first column).

1) I select the range including the headers, labels, and data and click the graph icon.
2) I select the XY scatter chart type
3) By default, the preview it shows treats each column as its own series (vs. column 1 on X axis and 2 on Y axis.
4) I change the "Series in" option to Rows.
5) Now it is showing a legend with a different color for each item (Dog, Cat, Horse, etc.), but the X-values are either 1 or 2 for all points.

I have found a way to have a true X-Y plot with series labels, if I click on the Series tab in this dialog and manually fix the X values and Y values from:
X: =Sheet1!$B$1:$C$1 to =Sheet1!$B$2
Y =Sheet1!$B$2:$C$2 to =Sheet1!$C$2
(for each series).

With large numbers of rows, this is laborious.

How can I do this more simply?

Answer 1

Your problem is that native Excel cannot label the individual data points as you would like ("Dog" at location 14,11; "Cat" at 10,13). At least that what 30 minutes of research has lead me to believe.



It appears that you need either a macro or an add-in to Excel to do what you want. I found a few of references to get you started:

http://www.windmill.co.uk/monitor41.html#Excel

http://www.windmill.co.uk/monitor67.html (scroll down to the Excel Corner)

http://www.bmsltd.co.uk/MVP/MVPPage.asp (scroll down to Rob Bovey's XYChartLabeler.exe)

http://www.tech-archive.net/pdf/Archive/Mac/microsoft.public.mac.office.excel/2008-06/msg00244.pdf (this one is much more recent, from July 2008)



I haven't tested any of these myself. Good luck!

Answer 2

Xy Chart Labeler

Answer 3

---EDIT---

I just tried this free Excel add-in. It worked like a champ and was easy. After you install it, you'll find a new item under the Tools menu.



AppsPro XY Chart Labeler

http://appspro.com/Utilities/ChartLabeler.htm



------------



Put your data as you describe above

Select all the data in all three columns

Make an XY Scatter chart

Right-click on one of the plot lines and select Format Data Series

Select the Data Labels tab

Check the X Value check box

OK

Do the same for the other plot line

Answer 4

To fix the chart do the following: Under the Chart menu, select Chart Options... Click on the Titles tab. There you can type in whatever name you want for the chart title and also for the x and y axes. Click on the Legend tab. There you can uncheck the Show legend box.