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

[mattgoof2005]

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.

 

[Jon Peltier]

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

 

[mattgoof2005]

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

 

[Tushar Mehta]

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/newsgroups/dynamic_chart_title/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

 

[Jon Peltier]

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

- Jon

 

[Elmer Smurdley]

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...

 

[Jon Peltier]

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

 

[nicholastoze]

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

 

[nicholastoze]

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

 

[Elmer Smurdley]

That worked! Thanks

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
_______