How do I put custom data labels on charts in Excel?
 

I have a chart showing burn rates (fairly small numbers) and EACs/ETCs(Relly
big numbers). I have set up 2 Y-Axis, but this stretches the burn rate a
lot, so if it changes a little bit, it adjusts the axis and suddenly, a 1%
spike goes all the way from the bottom edge to the top. I know I can define
the axis to compensate, but then if there really were a spike, it has the
potential to go off the page, something I'm not sure people will know how to
fix when I leave this internship in a couple weks.

What I want to do is add another set of data, a percent change in the burn
rate. Obviously, I don't want to show this as another line, but rather as a
label on the burn rate data points. This should be really easy, but I can't
find a way to do it. Also, I'm pretty iffy about downloading things as this
is a government-owned computer.

How do I put custom data labels on charts in Excel?
 

Matt -

There are a couple free Excel utilities that apply labels from a worksheet
range to a chart series. Both integrate seamlessly with Excel:

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

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


"mattgoof2005" wrote in message
...
I have a chart showing burn rates (fairly small numbers) and
EACs/ETCs(Relly
big numbers). I have set up 2 Y-Axis, but this stretches the burn rate a
lot, so if it changes a little bit, it adjusts the axis and suddenly, a 1%
spike goes all the way from the bottom edge to the top. I know I can
define
the axis to compensate, but then if there really were a spike, it has the
potential to go off the page, something I'm not sure people will know how
to
fix when I leave this internship in a couple weks.

What I want to do is add another set of data, a percent change in the burn
rate. Obviously, I don't want to show this as another line, but rather as
a
label on the burn rate data points. This should be really easy, but I
can't
find a way to do it. Also, I'm pretty iffy about downloading things as
this
is a government-owned computer.

How do I put custom data labels on charts in Excel?
 

I take it then that you can't do it without downloading something? (Frickin'
DOE computers)

"Jon Peltier" wrote:

Matt -

There are a couple free Excel utilities that apply labels from a worksheet
range to a chart series. Both integrate seamlessly with Excel:

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

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


"mattgoof2005" wrote in message
...
I have a chart showing burn rates (fairly small numbers) and
EACs/ETCs(Relly
big numbers). I have set up 2 Y-Axis, but this stretches the burn rate a
lot, so if it changes a little bit, it adjusts the axis and suddenly, a 1%
spike goes all the way from the bottom edge to the top. I know I can
define
the axis to compensate, but then if there really were a spike, it has the
potential to go off the page, something I'm not sure people will know how
to
fix when I leave this internship in a couple weks.

What I want to do is add another set of data, a percent change in the burn
rate. Obviously, I don't want to show this as another line, but rather as
a
label on the burn rate data points. This should be really easy, but I
can't
find a way to do it. Also, I'm pretty iffy about downloading things as
this
is a government-owned computer.

How do I put custom data labels on charts in Excel?
 

You can add custom labels by hand though it can be PITA for more than a few
labels. The technique for
Dynamic Chart Title
http://www.tushar-mehta.com/excel/ne...tle/index.html
also works with labels.


--
Regards,

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

In article ,
says...
I take it then that you can't do it without downloading something? (Frickin'
DOE computers)

"Jon Peltier" wrote:

Matt -

There are a couple free Excel utilities that apply labels from a worksheet
range to a chart series. Both integrate seamlessly with Excel:

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

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


"mattgoof2005" wrote in message
...
I have a chart showing burn rates (fairly small numbers) and
EACs/ETCs(Relly
big numbers). I have set up 2 Y-Axis, but this stretches the burn rate a
lot, so if it changes a little bit, it adjusts the axis and suddenly, a 1%
spike goes all the way from the bottom edge to the top. I know I can
define
the axis to compensate, but then if there really were a spike, it has the
potential to go off the page, something I'm not sure people will know how
to
fix when I leave this internship in a couple weks.

What I want to do is add another set of data, a percent change in the burn
rate. Obviously, I don't want to show this as another line, but rather as
a
label on the burn rate data points. This should be really easy, but I
can't
find a way to do it. Also, I'm pretty iffy about downloading things as
this
is a government-owned computer.

How do I put custom data labels on charts in Excel?
 

Can you download it at home and bring it in on a flash drive? Or are those
taboo, too?

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


"mattgoof2005" wrote in message
...
I take it then that you can't do it without downloading something?
(Frickin'
DOE computers)

"Jon Peltier" wrote:

Matt -

There are a couple free Excel utilities that apply labels from a
worksheet
range to a chart series. Both integrate seamlessly with Excel:

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

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


"mattgoof2005" wrote in message
...
I have a chart showing burn rates (fairly small numbers) and
EACs/ETCs(Relly
big numbers). I have set up 2 Y-Axis, but this stretches the burn rate
a
lot, so if it changes a little bit, it adjusts the axis and suddenly, a
1%
spike goes all the way from the bottom edge to the top. I know I can
define
the axis to compensate, but then if there really were a spike, it has
the
potential to go off the page, something I'm not sure people will know
how
to
fix when I leave this internship in a couple weks.

What I want to do is add another set of data, a percent change in the
burn
rate. Obviously, I don't want to show this as another line, but rather
as
a
label on the burn rate data points. This should be really easy, but I
can't
find a way to do it. Also, I'm pretty iffy about downloading things as
this
is a government-owned computer.

How do I put custom data labels on charts in Excel?
 

How does this work with data labels? I've tried every combination I
can think of to link a chart data point with a data label in an
adjacent column, to no avail.

I would be most appreciative of any help here...

On Wed, 26 Jul 2006 20:06:53 -0400, Tushar Mehta
wrote:

You can add custom labels by hand though it can be PITA for more than a few
labels. The technique for
Dynamic Chart Title
http://www.tushar-mehta.com/excel/ne...tle/index.html
also works with labels.

How do I put custom data labels on charts in Excel?
 

One by one, you could select a label (click once to select a series of
labels, then again to select a single label). type = in the formula bar,
then click on a cell.

To do a whole series at a time, check out one of these utilities:

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

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


"Elmer Smurdley" wrote in message
...
How does this work with data labels? I've tried every combination I
can think of to link a chart data point with a data label in an
adjacent column, to no avail.

I would be most appreciative of any help here...

On Wed, 26 Jul 2006 20:06:53 -0400, Tushar Mehta
wrote:

You can add custom labels by hand though it can be PITA for more than a
few
labels. The technique for
Dynamic Chart Title
http://www.tushar-mehta.com/excel/ne...tle/index.html
also works with labels.

How do I put custom data labels on charts in Excel?
 

Here's a technique for adding data labels that show up kind of like
tooltips. If you left-click and hold on a point it shows the data
label, then when you release the data label disappears. If you right-
click then the data label stays there (because the context menu
apparently interrupts the mouse-up event chain) and you can left click
to make it go away.

I've got the following in a sheet module (BTW, my chart uses dynamic
ranges that address parts of a pivottable. I do it this way, because
my chart x-axis is a date type and I want a natural spacing so I can't
use a pivotchart (which would use equal spacing for each date
'category')).

-----------shtXYZ
Option Explicit

Private my_labeller As PointDblClickLabeller

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.EnableEvents = False
Me.Calculate
Set my_labeller = New PointDblClickLabeller
my_labeller.init Me.ChartObjects(1).Chart,
flatten_array(Me.Range("PvtLabels").Value)
Application.EnableEvents = True
End Sub

Private Function flatten_array(data As Variant) As Variant
Dim i As Long, v As Variant
ReDim result(1 To 1)

i = UBound(result)
For Each v In data
If i < UBound(result) Then
ReDim Preserve result(1 To UBound(result) + 1)
End If
result(i) = v
i = i + 1
Next

flatten_array = result
End Function

---------Class PointDblClickLabeller
Option Explicit

Private WithEvents mChart As Excel.Chart

Private labels As Variant

Public Sub init(a_chart As Excel.Chart, some_labels As Variant)
Set mChart = a_chart
labels = some_labels
End Sub

Private Sub mChart_MouseDown(ByVal Button As Long, _
ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim elementId As Long, arg1 As Long, arg2 As Long
mChart.GetChartElement x, y, elementId, arg1, arg2
If elementId = xlSeries Then
If arg2 <= UBound(labels) Then
With mChart.SeriesCollection(arg1).Points(arg2)
Application.ScreenUpdating = False
.HasDataLabel = True
.DataLabel.Font.Size = 8
.DataLabel.Text = labels(arg2)
.DataLabel.Border.Weight = xlHairline
.DataLabel.Shadow = True
.DataLabel.Interior.Color = 13434879
.DataLabel.Position = xlLabelPositionAbove
Application.ScreenUpdating = True
End With
End If
End If
End Sub

Private Sub mChart_MouseUp(ByVal Button As Long, _
ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim elementId As Long, arg1 As Long, arg2 As Long
mChart.GetChartElement x, y, elementId, arg1, arg2
If elementId = xlSeries Then
With mChart.SeriesCollection(arg1).Points(arg2)
If .HasDataLabel Then
.DataLabel.Text = ""
.HasDataLabel = False
End If
End With
End If
End Sub

How do I put custom data labels on charts in Excel?
 

Here's an alternative class that uses mouse move (some of the
improvements below can be used in class above too).
But you get some flicker if there are a lot of data points;
unfortunately it doesn't seem possible to show/hide datalabels, have
keep recreating them and each step causes a flicker.

Class
------PointMouseMoveLabeller

Option Explicit

Private WithEvents mChart As Excel.Chart

Private labels As Variant
Private prevArg1 As Long
Private prevArg2 As Long

Public Sub init(a_chart As Excel.Chart, some_labels As Variant)
Dim v As Variant
Set mChart = a_chart
labels = some_labels

mChart.ProtectGoalSeek = True
For Each v In mChart.SeriesCollection
v.HasDataLabels = False
Next
End Sub

Private Sub mChart_MouseMove(ByVal Button As Long, ByVal Shift As
Long, ByVal x As Long, ByVal y As Long)
Dim elementId As Long, arg1 As Long, arg2 As Long
mChart.GetChartElement x, y, elementId, arg1, arg2
If elementId = xlSeries Then
' Uses same label whatever the series, so don't care about
arg1 (Series#) changes.
If prevArg2 < arg2 Then ' And prevArg1 < arg1 Then
unlabelLastPoint
prevArg1 = arg1
prevArg2 = arg2
If arg2 <= UBound(labels) Then
With mChart.SeriesCollection(arg1).Points(arg2)
Application.ScreenUpdating = False
.HasDataLabel = True
.DataLabel.Font.Size = 8
.DataLabel.Text = labels(arg2)
.DataLabel.Border.Weight = xlHairline
.DataLabel.Shadow = True
.DataLabel.Interior.Color = 13434879
.DataLabel.Position = xlLabelPositionAbove
Application.ScreenUpdating = True
End With
End If
End If
Else
unlabelLastPoint
End If
End Sub

Private Sub unlabelLastPoint()
On Error GoTo sub_end
With mChart.SeriesCollection(prevArg1).Points(prevArg2)
If .HasDataLabel Then
.HasDataLabel = False
End If
End With
sub_end:
prevArg1 = 0
prevArg2 = 0
End Sub

How do I put custom data labels on charts in Excel? Attn: Jon Peltier
 

That worked! Thanks

On Tue, 11 Sep 2007 14:55:24 -0400, "Jon Peltier"
wrote:

One by one, you could select a label (click once to select a series of
labels, then again to select a single label). type = in the formula bar,
then click on a cell.

To do a whole series at a time, check out one of these utilities:

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

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


"Elmer Smurdley" wrote in message
.. .
How does this work with data labels? I've tried every combination I
can think of to link a chart data point with a data label in an
adjacent column, to no avail.

I would be most appreciative of any help here...

On Wed, 26 Jul 2006 20:06:53 -0400, Tushar Mehta
wrote:

You can add custom labels by hand though it can be PITA for more than a
few
labels. The technique for
Dynamic Chart Title
http://www.tushar-mehta.com/excel/ne...tle/index.html
also works with labels.