Data Labels in PivotChart

[Guest]

I've created a very useful PivotChart. It sums up the areas occupied by business units for each floor in a building. The Chart Type is Stacked Bar. The Page Field is the Building. The Category axis is the Floor. The Pivot sums the square feet occupied by each business unit. What I want to finish this off with is to label each piece of the stack with the name of the business unit. Unfortunately the built-in data labelling options are limited to "value" - namely the area, or "label" - which shows the floor number

How do I get the business unit name (shown in the legend) onto the graph? I'm intrigued by Debra Dalgleish’s tool for labelling the last point in a series. When I run it against my chart, inserts the legend labels all right, but the top floor of the building is always the end of the series, and it labels zero values, so I get a chart with all the business unit names on the top floor - and all ones with no space printed over one another! Perhaps I'm looking to alter Debra's tool to label each non-zero data element the legend labels...

-Dave

 

[Jon Peltier]

Dave -

Rob Bovey's Chart Labeler (http://appspro.com) and John Walkenbach's
Chart Tools (http://j-walk.com) will both apply labels from a worksheet
range to a chart or pivot chart.

- Jon

 

[Canuck]

I've successfully augmented Debra Dalgleish's code to
label all the data elements, but still have the problem of
labels being generated for the zero-value items...

-Dave.

-----Original Message-----
I've created a very useful PivotChart. It sums up the

areas occupied by business units for each floor in a
building. The Chart Type is Stacked Bar. The Page Field
is the Building. The Category axis is the Floor. The
Pivot sums the square feet occupied by each business
unit. What I want to finish this off with is to label
each piece of the stack with the name of the business
unit. Unfortunately the built-in data labelling options
are limited to "value" - namely the area, or "label" -
which shows the floor number.

How do I get the business unit name (shown in the legend)

onto the graph? I'm intrigued by Debra Dalgleishâ?Ts tool
for labelling the last point in a series. When I run it
against my chart, inserts the legend labels all right, but
the top floor of the building is always the end of the
series, and it labels zero values, so I get a chart with
all the business unit names on the top floor - and all
ones with no space printed over one another! Perhaps I'm
looking to alter Debra's tool to label each non-zero data
element the legend labels....

 

[Debra Dalgleish]

Thanks for the credit, but if this is the 'Label Last Point' tool that
you're using, it's Jon Peltier's --

http://www.peltiertech.com/Excel/Charts/chartvba.html#LblLast

Occasionally those non-Canucks can come up with a useful utility, eh?

 

[Jon Peltier]

Dave -

After running Deb's code, change the number format of the labels to
something like this:

0.0;-0.0;"";

Adjust as necessary: the one I included uses 0.0 for positive numbers,
-0.0 for negatives, and "" for zeros. There's still technically a label,
but it has no characters.

- Jon

 

[Jon Peltier]

A name like Peltier isn't Canuck enough for you??

- Jon

 

[Debra Dalgleish]

Nice try, but your accent is all wrong.

A name like Peltier isn't Canuck enough for you??

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

 

[Guest]

You folks are great! ...and proper thanks to the code-writer

The data labels from the PivotTable legend are text strings (like "Visa Department"; my client is a bank!), so changing the number format doesn't have any effect. I've got the manual alternative to select data points one at a time an deleting them, but that's really tedious. I've got 100 points to delete in some cases. I'd prefer to only create the labels when the string length is > 0, but don't know what variable to test in the loop..

-Dave

 

[Jon Peltier]

Dave -

This code finds the last nonzero point and applies the label to it:

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long, iPt As Long
Dim ErrNum As Long
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, "No Chart
Selected"
Else
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
nPts = .Points.Count
For iPt = nPts To 1 Step -1
If .Values(iPt) <> 0 Then
On Error Resume Next
mySrs.Points(iPt).HasDataLabel = True
mySrs.Points(iPt).DataLabel.Text = mySrs.Name
ErrNum = Err.Number
On Error GoTo 0
If ErrNum = 0 Then Exit For
End If
Next
End With
Next
End If
End Sub

- Jon