Label formatting

[Brian Reilly, MS MVP]

Hi folks,
This is how to create a custom format for use in chart labels.

I have data already in the worksheet with decimal values e.g.
".25678%"
I want this to display in the chart label as "3" and not "3%"

What would be the best way(s) to accomplish this?

Thanks,
Brian Reilly, PowerPoint MVP

 

[Jim Cone]

Brian,

A way to accomplish this is to add a text box to the chart that
references a properly formatted cell...

1. If your number is in cell A12 then in another cell enter:
=ROUND(A12*1000,0)
2. Select the chart
3. In the formula bar enter an equal sign and click the cell with the formula.
4. A textbox (with the formula result) is automatically added to the chart.
Find it, right-click it, format it, size it and move to where you want the title.

Regards,
Jim Cone
San Francisco, CA

 

[Brian Reilly, MS MVP]

Jim,
Thanks, but that's not a workable solution. I know how to do that but
need to do it programmatically. I can add the textbox
programmatically, but cannot position it programmatically. And I have
lots meaning varying numbers) of data labels on each chart.

Jon or Tushar, you guys around?

Brian Reilly, PowerPoint MVP

 

[Stephen Bullen]

Hi Brian,

I have data already in the worksheet with decimal values e.g.
".25678%"
I want this to display in the chart label as "3" and not "3%"

Thanks, but that's not a workable solution. I know how to do that but
need to do it programmatically. I can add the textbox
programmatically, but cannot position it programmatically. And I have
lots meaning varying numbers) of data labels on each chart.

Jon or Tushar, you guys around?

I presume you mean either a value of "0.025678" displaying as "3", or ".25678" displaying as "26"

I think the best way will be to set the data labels yourself, using code:

Sub SetDataLabels()

Dim oCht As Chart
Dim iPt As Integer
Dim vaValues As Variant

Set oCht = Sheet1.ChartObjects(1).Chart

With oCht.SeriesCollection(1)
vaValues = .Values
For iPt = 1 To .Points.Count
.Points(iPt).DataLabel.Caption = Format$(vaValues(iPt) * 100, "0")
Next
End With

End Sub

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Tushar Mehta]

Hi Brian,

When you have Stephen you don't really need me, do you? {g}

But, in this case an alternative to Stephen's that I would choose would
be to put the desired result in a column and then use code to do the
functional equivalent of Bovey's XY Chartlabeler (ya think he'd rename
it someday?) or Walkenbach's Chart Tools. This way if the data change
the chart will update automatically.

--
Regards,

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

 

[Brian Reilly, MS MVP]

Stephen,
Lucky I didn't have to wait around for Jon (rofl).
Thanks,
Brian Reilly, PowerPoint MVP

 

[Brian Reilly, MS MVP]

Tushar,
A valuable insight, and thanks. But I'll use Stephen's suggestion
since the .sourcedatarange will vary periodically and what's another
itty bitty piece of code to run when that happens.

And besides who can resist using Stephen's brilliant suggestions
(vbg).

Thanks to both of you.

Brian Reilly, PowerPoint MVP

 

[Brian Reilly, MS MVP]

Stephen,
Picky, picky, picky (g).
Sweet solution for me. 'ta

Brian Reilly, PowerPoint MVP

 

[Jon Peltier]

Tushar -

Rob tells me the next version will just be the Chart Labeler, which should clear up
some confusion. I got an email from a guy who wondered if anyone had written a Bar
Chart labeler.

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