Thread: Line Chart - show percent and value?

I thought this would be easy, but I am really stumped!

I need to show the percentage in addition to the value in the data labels
for a line chart.

Example,
January February
Series 1 5 6
Series 2 15 4

I would like the January data label for Series 1 to show 5 (25%)
I would like the January data label for Series 2 to show 15 (75%)

I would like the February data label for Series 1 to show 6 (60%)
I would like the February data label for Series 2 to show 4 (40%)

TIA FOR ANY HELP!

You need to construct your labels in another worksheet range. For the
January Series 1 label it would look like

=B2&" ("&TEXT(B2/SUM(B2:B3),"0%))&")"

Then use one of these free Excel add-ins to apply the labels from the
worksheet range to the data series in the chart:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
774-275-0064
208-485-0691 fax
jon@peltiertech.com
http://PeltierTech.com/
_______


PK wrote:

> I thought this would be easy, but I am really stumped!
>
> I need to show the percentage in addition to the value in the data labels
> for a line chart.
>
> Example,
> January February
> Series 1 5 6
> Series 2 15 4
>
> I would like the January data label for Series 1 to show 5 (25%)
> I would like the January data label for Series 2 to show 15 (75%)
>
> I would like the February data label for Series 1 to show 6 (60%)
> I would like the February data label for Series 2 to show 4 (40%)
>
> TIA FOR ANY HELP!
>
>

Thanks Jon!

I am sure that would work, however this is my problem:

I am actually automating Excel from a MS Access database, and this
application will be distributed to many workstations which would not have the
add-ons.

Is there any way to do this without an add-on?






"Jon Peltier" wrote:

> You need to construct your labels in another worksheet range. For the
> January Series 1 label it would look like
>
> =B2&" ("&TEXT(B2/SUM(B2:B3),"0%))&")"
>
> Then use one of these free Excel add-ins to apply the labels from the
> worksheet range to the data series in the chart:
>
> Rob Bovey's Chart Labeler, http://appspro.com
> John Walkenbach's Chart Tools, http://j-walk.com/ss
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> 774-275-0064
> 208-485-0691 fax
> jon@peltiertech.com
> http://PeltierTech.com/
> _______
>
>
> PK wrote:
>
> > I thought this would be easy, but I am really stumped!
> >
> > I need to show the percentage in addition to the value in the data labels
> > for a line chart.
> >
> > Example,
> > January February
> > Series 1 5 6
> > Series 2 15 4
> >
> > I would like the January data label for Series 1 to show 5 (25%)
> > I would like the January data label for Series 2 to show 15 (75%)
> >
> > I would like the February data label for Series 1 to show 6 (60%)
> > I would like the February data label for Series 2 to show 4 (40%)
> >
> > TIA FOR ANY HELP!
> >
> >
>

Sure, the code that the add-in uses to label the points can be run from
the automation routine in Access.

(Also, once the labels are created and linked to the cells, the workbook
does not need the add-in to display correctly.)

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


PK wrote:

> Thanks Jon!
>
> I am sure that would work, however this is my problem:
>
> I am actually automating Excel from a MS Access database, and this
> application will be distributed to many workstations which would not have the
> add-ons.
>
> Is there any way to do this without an add-on?
>
>
>
>
>
>
> "Jon Peltier" wrote:
>
>
>>You need to construct your labels in another worksheet range. For the
>>January Series 1 label it would look like
>>
>>=B2&" ("&TEXT(B2/SUM(B2:B3),"0%))&")"
>>
>>Then use one of these free Excel add-ins to apply the labels from the
>>worksheet range to the data series in the chart:
>>
>>Rob Bovey's Chart Labeler, http://appspro.com
>>John Walkenbach's Chart Tools, http://j-walk.com/ss
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>774-275-0064
>>208-485-0691 fax
>>jon@peltiertech.com
>>http://PeltierTech.com/
>>_______
>>
>>
>>PK wrote:
>>
>>
>>>I thought this would be easy, but I am really stumped!
>>>
>>>I need to show the percentage in addition to the value in the data labels
>>>for a line chart.
>>>
>>>Example,
>>> January February
>>>Series 1 5 6
>>>Series 2 15 4
>>>
>>>I would like the January data label for Series 1 to show 5 (25%)
>>>I would like the January data label for Series 2 to show 15 (75%)
>>>
>>>I would like the February data label for Series 1 to show 6 (60%)
>>>I would like the February data label for Series 2 to show 4 (40%)
>>>
>>>TIA FOR ANY HELP!
>>>
>>>
>>

Hi PK,

Sample Excel code to label chart points is:

Dim pts As Points
Dim LabelRange As Range
Dim stRow As Integer
Dim stCol As Integer
Dim i As Integer

' This example assumes that data and data labels are in columns

Set LabelRange = Range("B1:B6")

stRow = LabelRange.Row
stCol = LabelRange.Column

' you could prompt for chart name here
Set pts = ActiveSheet.ChartObjects("Chart 1").Chart. _
SeriesCollection(1).Points

' now show the data label for each point and set the text
' to that in the selected range
For i = 1 To pts.Count
pts(i).ApplyDataLabels Type:=xlShowValue
pts(i).DataLabel.Text = "=Sheet1!R" & i + stRow - 1 & "C" & stCol
Next i

You should be able to automate from Access using something similar.

Ed Ferrero
http://edferrero.m6.net/


> Thanks Jon!
>
> I am sure that would work, however this is my problem:
>
> I am actually automating Excel from a MS Access database, and this
> application will be distributed to many workstations which would not have
> the
> add-ons.
>
> Is there any way to do this without an add-on?
>
>
>
>
>
>
> "Jon Peltier" wrote:
>
>> You need to construct your labels in another worksheet range. For the
>> January Series 1 label it would look like
>>
>> =B2&" ("&TEXT(B2/SUM(B2:B3),"0%))&")"
>>
>> Then use one of these free Excel add-ins to apply the labels from the
>> worksheet range to the data series in the chart:
>>
>> Rob Bovey's Chart Labeler, http://appspro.com
>> John Walkenbach's Chart Tools, http://j-walk.com/ss
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services
>> 774-275-0064
>> 208-485-0691 fax
>> jon@peltiertech.com
>> http://PeltierTech.com/
>> _______
>>
>>
>> PK wrote:
>>
>> > I thought this would be easy, but I am really stumped!
>> >
>> > I need to show the percentage in addition to the value in the data
>> > labels
>> > for a line chart.
>> >
>> > Example,
>> > January February
>> > Series 1 5 6
>> > Series 2 15 4
>> >
>> > I would like the January data label for Series 1 to show 5 (25%)
>> > I would like the January data label for Series 2 to show 15 (75%)
>> >
>> > I would like the February data label for Series 1 to show 6 (60%)
>> > I would like the February data label for Series 2 to show 4 (40%)
>> >
>> > TIA FOR ANY HELP!
>> >
>> >
>>

Ed - that is EXACTLY what I was looking for!!!! THANK YOU!!!!

Jon - Thanks for your help also!





"Ed Ferrero" wrote:

> Hi PK,
>
> Sample Excel code to label chart points is:
>
> Dim pts As Points
> Dim LabelRange As Range
> Dim stRow As Integer
> Dim stCol As Integer
> Dim i As Integer
>
> ' This example assumes that data and data labels are in columns
>
> Set LabelRange = Range("B1:B6")
>
> stRow = LabelRange.Row
> stCol = LabelRange.Column
>
> ' you could prompt for chart name here
> Set pts = ActiveSheet.ChartObjects("Chart 1").Chart. _
> SeriesCollection(1).Points
>
> ' now show the data label for each point and set the text
> ' to that in the selected range
> For i = 1 To pts.Count
> pts(i).ApplyDataLabels Type:=xlShowValue
> pts(i).DataLabel.Text = "=Sheet1!R" & i + stRow - 1 & "C" & stCol
> Next i
>
> You should be able to automate from Access using something similar.
>
> Ed Ferrero
> http://edferrero.m6.net/
>
>
> > Thanks Jon!
> >
> > I am sure that would work, however this is my problem:
> >
> > I am actually automating Excel from a MS Access database, and this
> > application will be distributed to many workstations which would not have
> > the
> > add-ons.
> >
> > Is there any way to do this without an add-on?
> >
> >
> >
> >
> >
> >
> > "Jon Peltier" wrote:
> >
> >> You need to construct your labels in another worksheet range. For the
> >> January Series 1 label it would look like
> >>
> >> =B2&" ("&TEXT(B2/SUM(B2:B3),"0%))&")"
> >>
> >> Then use one of these free Excel add-ins to apply the labels from the
> >> worksheet range to the data series in the chart:
> >>
> >> Rob Bovey's Chart Labeler, http://appspro.com
> >> John Walkenbach's Chart Tools, http://j-walk.com/ss
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Peltier Technical Services
> >> 774-275-0064
> >> 208-485-0691 fax
> >> jon@peltiertech.com
> >> http://PeltierTech.com/
> >> _______
> >>
> >>
> >> PK wrote:
> >>
> >> > I thought this would be easy, but I am really stumped!
> >> >
> >> > I need to show the percentage in addition to the value in the data
> >> > labels
> >> > for a line chart.
> >> >
> >> > Example,
> >> > January February
> >> > Series 1 5 6
> >> > Series 2 15 4
> >> >
> >> > I would like the January data label for Series 1 to show 5 (25%)
> >> > I would like the January data label for Series 2 to show 15 (75%)
> >> >
> >> > I would like the February data label for Series 1 to show 6 (60%)
> >> > I would like the February data label for Series 2 to show 4 (40%)
> >> >
> >> > TIA FOR ANY HELP!
> >> >
> >> >
> >>
>
>
>