Adding Data Information to a Chart

[Mike M]

I have a stacked bar chart, with the stacks representing total hours o
various groupings of personal.

I would like to add data to the chart, but here is the problem. It i
easy to add the data value, category name, and/or series name to th
chart using the Format Data Series options. However, I need to add dat
from another area on the data sheet, which are the numbers representin
how many persons are currently in each grouping.

Data example:
HOURS NUMBERS
Group A Group B Group C Group A Group B
Group C
Nov 1 1000 750 500 10
7 3
Nov 7 1121 689 505 11
6 3

So the hours (1000, 750, 500) form a stack for each appropriate dat
(with the total stack being total hours for that date) but in eac
stacked range, is it possible to have the 10, 7, and 3 added to th
chart **without having to add a text box linked to each appropriat
cell**. Thusly the 10 whould appear in the 1000 hour stack, the 7 i
the 750, etc. When I tried the linked text box option, each time a ne
data set is added, the Y axis scale MAY change if the hours range mov
sufficiently, making it necessary to always readjust the text bo
locations. Also every time new data is added, you have to creat
another text box for each hours group - very tmmie consuming (as o
course my example here is only a very basic version of the more comple
actual chart.

Any thought appreciated, and thanks in advance.
Mik

 

[Mike M]

I found my answer. It seams that an add-in is required. I am using the
XY Chart Labeler by appspro.com. It now does exactly what I was looking
for.

 

[Jon Peltier]

Mike -

If you only have one or two labels, you can add a datalabel using any of the
default options (value, label, series title), then use one of these
approaches:

(a) to make a static label, select the series of labels (one single click),
then the specific label (another single click), then click in the text of
the label and edit the text.

(b) to make a label linked to a cell, select the series of labels (one
single click), then the specific label (another single click), then click in
the formula bar, type the equals key, and click on the cell.

Rob Bovey's Chart Labeler automates (b) for a whole series at once.

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

 

[Mike M]

Thanks Jon,

I did try your method a and b, but with up to 50 alternate labels on
the chart, it was very time consuming - and I did have problems with
the labels not following the stacks if the chart scale changed as new
data was added, making even more manual adjusting.

I was Rob's chart labeler that I found and am now using - and that does
exactly what I was looking for. It replaces the auto generated value
label, which always follows the stack scale, with any text or number of
your choice, by looking an any cell rage specified.

I can now just copy and paste the data into the data sheet, and the
charts all auto generate, including my alternate values in all the
various stacks.

Mike

 

[Jon Peltier]

Hi Mike -

I didn't mean to have you do it all manually, I was just explaining that you
can customize a data label, and Rob's tool lets you do it in bulk. It's one
of my favorite and most used add-ins.

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