Line chart, value labeling

[NYBoy]

I like to change line graph settings so that only one value will appea
on the line (not all the values).
I right clicked on the point on the line, selected format data series
selected Data Labels tab, checked box Show Values. and then click ok.
All the data values show up on the line. But, I like to see only on
specific value. How do we do that

 

[Andy Pope]

Hi,

You need to select the line series and then select a single point before
displaying the format dialog and enabling data labels.

Cheers
Andy

 

[NYBoy]

Thanks Andy, It works!!

One more question.
It would be the last point. Every month I update the data and the lin
extends and new data is entered. I always want to see the value of las
point. Would it work

 

[Andy Pope]

Jon Peltier's has a page on the very subject!

http://peltiertech.com/Excel/Charts/LabelLastPoint.html

 

[NYBoy]

Thanks Andy,
I am not familiar with Excel VBA project stuff. I don't know where t
paste once I copied the commands.
I think I am gonna have ask my boss to do it

 

[NYBoy]

How do we close the thread

 

[NYBoy]

How do we close the thread? :confused

 

[Andy Pope]

If you have the workbook with the chart open use ALT+F11 to get to the
VBE (Visual Basic Editor/Environment).
Use the Insert menu to add a Module.
Paste the code from Jon's page.

ALT+F11 to return to your workbook.
select the chart and the press ALT+F8 to display the Macro dialog.
From here run the LastPointLabel Macro.

To 'close' the thread just post a reply to say the solution worked.

Cheers
Andy

 

[NYBoy]

Thanks Andy, It somewhat worked.
But it gave me the Title (of the line from that row) instead of th
mathematical value of the last point

 

[NYBoy]

This is the first time I am visiting the Exceltip.com....it's prett
cool..

Andy, I went to your website and downloaded your clocks. When I clic
Start, a msg pop up saying "macros are disabled....". Is it just me o
my computer?

 

[Jon Peltier]

NY -

By default, the security settings in Excel are high. If you set them to
medium, you at least have an option to enable macros in each workbook
you open. On the Tools menu, select Macros, then Security, and on the
Security Level tab, select Medium.

- Jon

 

[NYBoy]

Gentlemen, It doesn't give the mathematical value. Instead, it gives m
the legand of the line (e.g. Monthly Performace).
I am looking for the value of the last point.
By the ways, you guys are great help...I learn a lot from here...


*********************************************
Re: Line chart, value labeling
If you have the workbook with the chart open use ALT+F11 to get to the
VBE (Visual Basic Editor/Environment).
Use the Insert menu to add a Module.
Paste the code from Jon's page.

ALT+F11 to return to your workbook.
select the chart and the press ALT+F8 to display the Macro dialog.
From here run the LastPointLabel Macro.

To 'close' the thread just post a reply to say the solution worked.

Cheers
Andy

NYBoy < wrote

 

[Andy Pope]

Just a little tweak to the code is needed.

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
nPts = .Points.Count
mySrs.Points(nPts).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
mySrs.Points(nPts).DataLabel.Text = mySrs.Values ' <**
End With
Next
End Sub

Cheers
Andy

 

[NYBoy]

Andy, It worked. It gave me the value of the last point (for month
April) but.....

Just to test...As I entered the new data in the spreadsheet for next
month, those values didn't role to the next point.
I was expecting that it would give me new values for the new last
point...
Sorry to bother you again..

 

[Andy Pope]

You did run the macro again once the data changed.
Also note the additional code line to clear any previous labels.

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
.HasDataLabels = False ' Remove previous labels
nPts = .Points.Count
mySrs.Points(nPts).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
mySrs.Points(nPts).DataLabel.Text = mySrs.Values ' <**
End With
Next
End Sub

 

[NYBoy]

Andy, It worked great. I copied your new code and pasted and run the
macro again....

But, still my goal is not to touch the chart at all. Can we also set so
that the marco would run automatically.....
If there is no way, then I can live with whatever I have so far...

but another thing is, everytime I run the marcos, text size, color,
etc... changes. Is there a permanent fix?

 

[NYBoy]

Andy, It worked great. I copied your new code and pasted and run th
macro again....

But, still my goal is not to touch the chart at all. Can we also set s
that the marco would run automatically.....
If there is no other way, then I can live with whatever I have s
far...

but another thing is, everytime I run the marcos, text size, color
etc... changes. Is there a permanent fix

 

[Andy Pope]

You will need to add the formatting to the macro. Use the macro recorder
whilst resetting the information to get the code required.

I have added a page to my site that shows how to label the last point
using dummy data series. This maybe more appropriate if you have dynamic
charts.
<http://www.andypope.info/charts/Labellast.htm>

Cheers
Andy

 

[NYBoy]

Andy, Can I send you the file? May be you can do it the quick way

 

[Andy Pope]

Yes, email it to me off list.

Andy, Can I send you the file? May be you can do it the quick way.