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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
I take it then that you can't do it without downloading something? (Frickin'
DOE computers)
 
Can you download it at home and bring it in on a flash drive? Or are those
taboo, too?

- Jon
 
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...
 
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
 
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
 
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
 
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
_______
Click to expand...
 
Back
Top