data labels not linked to data source

[viveleroi0]

I have a chart that displays two numbers in a stacked bar chart... but
the data labels are integers, which the source data is.

I want the data labels to display the percentage that source column A
is of B...

For example the source data is A1: 5, B1: 10, and the chart displays
the data label as 5 and 10 respectively.

I want the data label to show 50% (A1/B1) instead.

How can I do that?

 

[Debra Dalgleish]

You can use Rob Bovey's XY Chart Labeler to add the labels. It's a free
add-in that you can download from his web site:

http://www.appspro.com/Utilities/ChartLabeler.htm

 

[Stephanie Krieger]

You don't need to install an addin to do this -- it's
easy to do just with Excel. I'm emailing you a sample
workbook to demonstrate these steps:

1. Create the % calculations as another column on your
data worksheet (if you don't want it to appear on your
worksheet, you can hide the column after the chart is
setup).

Then, select the first individual data label that you
want to display the percentage (to do that, select the
series of data labels and then click once on the
individual label to select only that one).

Don't click into the data label, but with it selected,
click into the formula bar and type the equal sign,
followed by the cell reference containing the percentage
you need (once you type the equal sign, you can also
browse to and select the data cell instead of typing it
in). Then, press Enter to set. The data label will be
dynamically linked to that percentage, which is
calculated from your actual data.

If your chart is very large (that is, if there are too
many data labels to do this in a reasonable period of
time) or if its something you do frequently -- you can
automate this in VBA pretty easily. Let me know if that
info would be useful.

Hope this helps.

Stephanie Krieger
author of Microsoft Office Document Designer
email: (e-mail address removed)
blog: arouet.net

 

[Tushar Mehta]

You don't need to install an addin to do this -- it's
{snip}

time) or if its something you do frequently -- you can
automate this in VBA pretty easily. Let me know if that
info would be useful.

And, reinventing the wheel in VBA is preferable to using an add-in
because...

--
Regards,

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

 

[Stepahnie Krieger]

Doesn't require reinventing the wheel at all. The steps I
provided, as you saw, were very fast and simple in the
UI. However, if it is something you do frequently, you
can automate the process with very simple VBA. I wouldn't
consider a short macro to be reinventing anything -- and
you only need to even take that step if you want to
automate what's already a fast and easy task.

Stephanie

-----Original Message-----

says...

You don't need to install an addin to do this -- it's
{snip}

time) or if its something you do frequently -- you can
automate this in VBA pretty easily. Let me know if that
info would be useful.

And, reinventing the wheel in VBA is preferable to using an add-in
because...

--
Regards,

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

 

[Stephanie Krieger]

Hi Tushar ... I should have added this originally. Feel
free to drop me an email if you would like a copy of the
native Excel solution that I sent to the person who
originally posted the request.

Stephanie

-----Original Message-----

says...

You don't need to install an addin to do this -- it's
{snip}

time) or if its something you do frequently -- you can
automate this in VBA pretty easily. Let me know if that
info would be useful.

And, reinventing the wheel in VBA is preferable to using an add-in
because...

--
Regards,

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

 

[Jon Peltier]

Stephanie -

What Tushar was getting at is that several free addins are available on
the web to automate this process very nicely. Rob Bovey's Chart Labeler
(http://appspro.com) is one of the most widely used, and it has some
nice features. It allows you to specify a range which has data labels
for the entire series, and it makes sure that the range has as many
cells as the series has points. Second, it doesn't put the text into the
data labels, it links the labels to the cells so the labels update as
the cells change. Third, it copies the cell formats onto the data label
formats. Fourth, it incorporates the positions of the labels right in
the initial dialog. Finally, it allows you to move a series of labels,
or just a single label, with little arrow buttons, which allows much
finer control than is possible with the mouse.

The code to apply data labels to points is widely available:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q161513

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

 

[Tushar Mehta]

Thank you for that offer. I've had a web page about using that method
for a data label and a chart title for several years now. It is also
very effective when used with a text box inserted into a chart.

Dynamic Chart Title
http://www.tushar-
mehta.com/excel/newsgroups/dynamic_chart_title/index.html

As far as 'simple VBA' goes, we will have to disagree. My Hover Chart
Labels add-in (http://www.tushar-
mehta.com/excel/software/chart_hover_label/index.html) has a bare-bones
capability to create dynamic labels. Just the userform related code is
over 120 lines -- and that is in my particular style of writing compact
code. True, the rest of the code -- sans the goodies of a more
sophisticated add-in -- is short. It's about 20 lines. But in any
general purpose utility, 80-90% of the code is invariably associated
with the UI, so that should not be a surprise.

--
Regards,

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

Hi Tushar ... I should have added this originally. Feel
free to drop me an email if you would like a copy of the
native Excel solution that I sent to the person who
originally posted the request.

Stephanie

-----Original Message-----

 

[Stephanie Krieger]

Thanks, Jon -- I appreciate the detail on that addin.

Certainly sounds like a nice tool -- I just think its
usually simpler and faster to let the application do what
it can do well on its own. The one thing the addin does,
as you described, that you can't automatically do in the
user interface is specify the full data series at once
when linking the labels to a custom source -- that's
nice -- it's the thing I'd write a macro to do ... which
is personal preference, I think, unless the addin offers
additional perks you can't accomplish in the standard UI.
(As you saw in the steps I provided, and I'm sure you
know, it's super easy to link the labels to a custom
source that updates, and carries over the number format
(that last part is Excel default behavior for any linked
text boxes or labels) right on the chart itself with no
addins or code).

I'm curious to see if it offers other options (like more
flexible scatter\bubble chart labeling than you can
automatically do in the UI). I'll check it out. Always
nice to discover new things.

Thanks again for the info.

Stephanie