Point labels in an Excel scatter plot to be associated text

[Point labels in scatter plot - text?]

I am trying to plot a scatter plot where I would like each point to have a
specific name. For instance, I have three columns, company name, total
revenue, and % margin. I would like to plot revenu vs. % margin and when
clicking on a specific point, I would like to find out the company name.

[Tushar Mehta]

Check out Rob Bovey's Chartlabeler at www.appspro.com or John
Walkenbach's Chart Tools at www.j-walk.com.

If you want to see the labels only when you hover the mouse over a
point see
Hover Chart Label
http://www.tushar-mehta.com/excel/so...bel/index.html
Note that the add-in includes a chart labeler capability.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, =?Utf-
8?B?UG9pbnQgbGFiZWxzIGluIHNjYXR0ZXIgcGxvdCAtIHRleHQ/?= <Point labels in
scatter plot - [email protected]> says...
> I am trying to plot a scatter plot where I would like each point to have a
> specific name. For instance, I have three columns, company name, total
> revenue, and % margin. I would like to plot revenu vs. % margin and when
> clicking on a specific point, I would like to find out the company name.
>

[k_thakur]

I am trying to plot a scatter plot where I would like each point to have a
specific name. For instance, I have three columns, company name, total
revenue, and % margin. I would like to plot revenu vs. % margin and when
clicking on a specific point, I would like to find out the company name.

Hi, There is very elegant way of doing this in Excel without any third party add-ins. I explain you how to do this in following steps:
Step1: Organize your data as follows
A B C
1 Company Revenue (X) Margin (Y)
2 C1 200 12
3 C2 100 20

Step2: Plot a XY Scatter plot using MS Excel®, by selecting only B1:C3. Select series in: Column (don’t select Series in: Rows). This would produce your desired Revenue vs. %Margin Plot with Revenue on the XAxis & %Margin on YAxis. Have your leveling of axes done.

Step3: Click on the data point on the XY scatter it would select the data series. Then click once again on the individual data point to select it alone say for instance, point (Revenue=200, %Margin=12). Right click on the selected data point -->Select Format Data Series-->Select Data Label-->Show Value

Step4: This would show the value of %Margin (= 20 in this case) beside the selected data point (Revenue=200, %Margin=12). Now double click on the label you have just created, this would give you a text box surrounding your label. Write company name in this text box (C1 in this case). Do the same for other data points as well. Now you have the third data value referenced to your data points. This is dynamic, even if you change the source data value (say the Revenue/Margin), company name would move along with the data point.

Alternatively, you have to buy/download some Excel Ad-ins that lets you show data label. One such free download is available at: http://www.xl-logic.com/pages/addins.html download the xy-levels.zip.
Hope this helps you :-)
Regards,
Kartik Thakur
Email: [email protected]

[Jon Peltier]

You neglected to explain how to make the label dynamic in terms of
pointing to the cell, so that the data label in the chart changes when
the label in the cell changes. Select the series of labels, then select
the specific label (two single clicks), then without clicking to put the
cursor into the pseudo-text box, type the equals key, then click on the
cell with the mouse.

Both of the following free add-ins link the the cell in this way. Rob's
has the added feature that it copies the cell's text formatting to the
data label, although the formatting remains what it was when the label
was created, and doesn't change if you change the formatting in the
worksheet.

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com/ss

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

The manual technique is nice to know, but becomes tedious after about
the third label.

k_thakur wrote:

> Point labels in scatter plot - text? Wrote:
>
>>I am trying to plot a scatter plot where I would like each point to have
>>a
>>specific name. For instance, I have three columns, company name,
>>total
>>revenue, and % margin. I would like to plot revenu vs. % margin and
>>when
>>clicking on a specific point, I would like to find out the company
>>name.
>
>
> Hi, There is very elegant way of doing this in Excel without any third
> party add-ins. I explain you how to do this in following steps:
> Step1: Organize your data as follows
> A B C
> 1 Company Revenue (X) Margin (Y)
> 2 C1 200 12
> 3 C2 100 20
>
> Step2: Plot a XY Scatter plot using MS Excel®, by selecting only B1:C3.
> Select series in: Column (don’t select Series in: Rows). This would
> produce your desired Revenue vs. %Margin Plot with Revenue on the XAxis
> & %Margin on YAxis. Have your leveling of axes done.
>
> Step3: Click on the data point on the XY scatter it would select the
> data series. Then click once again on the individual data point to
> select it alone say for instance, point (Revenue=200, %Margin=12).
> Right click on the selected data point -->Select Format Data
> Series-->Select Data Label-->Show Value
>
> Step4: This would show the value of %Margin (= 20 in this case) beside
> the selected data point (Revenue=200, %Margin=12). Now double click on
> the label you have just created, this would give you a text box
> surrounding your label. Write company name in this text box (C1 in this
> case). Do the same for other data points as well. Now you have the third
> data value referenced to your data points. This is dynamic, even if you
> change the source data value (say the Revenue/Margin), company name
> would move along with the data point.
>
> Alternatively, you have to buy/download some Excel Ad-ins that lets you
> show data label. One such free download is available at:
> http://www.xl-logic.com/pages/addins.html download the
> xy-levels.zip.
> Hope this helps you :-)
> Regards,
> Kartik Thakur
> Email: [email protected]
>
>

[colej1390]

Microsoft has answered this question (using VBA)

http://support.microsoft.com/kb/914813

[MichelleJohnson]

Dear Colej1390, thank you for your information. I want to add the labels only when I hover the mouse over a
point for a scatter plot, but I cannot figure it out how to do it. Could you please help me on this? I appreciate your help.

[MichelleJohnson]

Microsoft has answered this question (using VBA)

http://support.microsoft.com/kb/914813

Dear Colej1390, thank you for your information. I want to add the labels only when I hover the mouse over a
point for a scatter plot, but I cannot figure it out how to do it. Could you please help me on this? I appreciate your help.

[FDibbins]

Michelle, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[emassey]

It isn't clear what your rows and columns are:> Step1: Organize your data as follows
> A B C
> 1 Company Revenue (X) Margin (Y)
> 2 C1 200 12
> 3 C2 100 20

Is A 1, 2, 3, etc. or is
A: Company C1, C2, C3, etc.
B: Revenue (X)
C: Margin (Y)

You have two columns for A.

[emassey]

The method you describe works BUT it is annoying and redundant to have to go to EACH data point and re-label it.
There should be an option that changes the entire data series to a specific label desired, whether it be a Name, X or Y value.
I am trying to include the label name (geochemical oxide) to each data point (about 40 per x-y scatterplot) and have 8 plots to do. That is 320 labels that need the names included. How can I request this in Excel so that I do not have to relabel them one-by-one as you describe?

[tiljes]

I am very sorry. I thought I had figured it out with the transposing. It seemed to work, but I didn't look carefully. So disregard my post. But I guess this is the reason why many scientists often are forced to use programs specifically designed for plotting graphs like Origin or Golden's Grapher. But what is missing here with the hovering would be a really useful addition to Excel!

@emassey, fellow geochemist writing here.

The problem is that Excel doesn't like the way geochemical data is organized. It seems to be a very untypical way so the Excel programmers never thought of it all the way through to Office 2016. So the solution from k_thakur was correct, but he explained it a bit difficult.

What you have to do is to transpose your data. So, create a new empty sheet in your excel file. Then select all your data -> copy -> go to new sheet -> right click first cell -> paste special -> transpose.

Right now, your data looks as follows:
Name SiO2 K2O Na2O
rock1 0.40 .10 .10
rock2 0.45 .15 .5
rock3 .50 .18 .4

But for Excel to show the correct rock1, rock2, rock3 when hovering over a point in the scatter plot, you need to have your data as follows:
Name rock1 rock2 rock3
SiO2 .40 .45 .50
K2O .10 .15 .18
Na2O .10 .5 .4

That is why you do the transpose paste. I know it is very annoying and makes the table unnecessary wide (because sample names take more horizontal space than oxide formulas), but that's the only way to do it without using add-ins.