Leader Lines - Stacked Column Charts

Help! Is there any way to use automatic leader lines to the data labels with
stacked column charts???
Thanks!

Hi,

Pie charts are the only ones with built-in leader lines.
Stack column charts only have the option of positioning the labels Top,
Middle or bottom of column.

Are you manually moving the data labels?

I guess, as long as the chart is not 3d, you could fake the leader lines
using dummy xy scatter series.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"suejay" wrote in message
...
Help! Is there any way to use automatic leader lines to the data labels
with
stacked column charts???
Thanks!

Hi Andy-
Thanks for your suggestion - yes, we are forced to manually move the data
labels because the charts need to be small enough to fit on a PowerPoint
slide. How would I fake the leader lines with a dummy xy scatter series?
It's worth a try!

"Andy Pope" wrote:

Hi,

Pie charts are the only ones with built-in leader lines.
Stack column charts only have the option of positioning the labels Top,
Middle or bottom of column.

Are you manually moving the data labels?

I guess, as long as the chart is not 3d, you could fake the leader lines
using dummy xy scatter series.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"suejay" wrote in message
...
Help! Is there any way to use automatic leader lines to the data labels
with
stacked column charts???
Thanks!

Create a stacked column chart on the range A1:C5 , where that range contains
the following data
Note: empty is used to denote actual empty cell if included it will be
treated as zero.

empty Data1 Data2
a 1 2
b 2 3
c 3 4
d 4 3

Use the Source data dialog to add 2 more series called DataLabels and
LeaderLines
Select the Datalabels series and change to xy scatter chart type.
Repeat for Leaderlines series

Add the following data to range E1:G9
X Y Label
1.5 0.5 a1
2.5 1 b1
3.5 1.5 c1
4.5 2 d1
1.5 2 a2
2.5 3.5 b2
3.5 5 c2
4.5 5.5 d2

Via the source data dialog set the range for the Datalabels series to be
XValues: =E2:E9
YValues: =F2:F9

Add the following data to range I1:J24
X Y
1.5 0.5
1 0.5
empty empty
2.5 1
2 1
empty empty
3.5 1.5
3 1.5
empty empty
4.5 2
4 2
empty empty
1.5 2
1 2
empty empty
2.5 3.5
2 3.5
empty empty
3.5 5
3 5
empty empty
4.5 5.5
4 5.5

Via the source data dialog set the data for LeaderLines series to be
XValues: =I2:I24
YValues: =J2:J24

Format LeaderLines series to have no marker a line style of choice.

Format datalabels series to have no marker or line style.
You need to apply data labels and link those to the range G2:G9. Quickest
way to do this is with this free add-in.
http://www.appspro.com/Utilities/ChartLabeler.htm

You should now have a stacked column chart with data labels to the right of
each column centered within each point.
By adjusting the datalabel and leaderlines x any positions you can place the
labels where ever you linke within the plotarea.
You can even use formula to calculate the positions so you have dynamic
positioning.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"suejay" wrote in message
...
Hi Andy-
Thanks for your suggestion - yes, we are forced to manually move the data
labels because the charts need to be small enough to fit on a PowerPoint
slide. How would I fake the leader lines with a dummy xy scatter series?
It's worth a try!

"Andy Pope" wrote:

Hi,

Pie charts are the only ones with built-in leader lines.
Stack column charts only have the option of positioning the labels Top,
Middle or bottom of column.

Are you manually moving the data labels?

I guess, as long as the chart is not 3d, you could fake the leader lines
using dummy xy scatter series.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"suejay" wrote in message
...
Help! Is there any way to use automatic leader lines to the data
labels
with
stacked column charts???
Thanks!

Instead of relying on leader lines, is there another way to identify the
items in the chart? Or perhaps is there a different way to lay out your
chart so the labeling is less critical to the display of information?

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


"suejay" wrote in message
...
Help! Is there any way to use automatic leader lines to the data labels
with
stacked column charts???
Thanks!

Thanks, Andy - it worked - it still poses a challenge because 3 of the
stacked columns are really tiny. I appreciate all your help!
Sue

"Andy Pope" wrote:

Create a stacked column chart on the range A1:C5 , where that range contains
the following data
Note: empty is used to denote actual empty cell if included it will be
treated as zero.

empty Data1 Data2
a 1 2
b 2 3
c 3 4
d 4 3

Use the Source data dialog to add 2 more series called DataLabels and
LeaderLines
Select the Datalabels series and change to xy scatter chart type.
Repeat for Leaderlines series

Add the following data to range E1:G9
X Y Label
1.5 0.5 a1
2.5 1 b1
3.5 1.5 c1
4.5 2 d1
1.5 2 a2
2.5 3.5 b2
3.5 5 c2
4.5 5.5 d2

Via the source data dialog set the range for the Datalabels series to be
XValues: =E2:E9
YValues: =F2:F9

Add the following data to range I1:J24
X Y
1.5 0.5
1 0.5
empty empty
2.5 1
2 1
empty empty
3.5 1.5
3 1.5
empty empty
4.5 2
4 2
empty empty
1.5 2
1 2
empty empty
2.5 3.5
2 3.5
empty empty
3.5 5
3 5
empty empty
4.5 5.5
4 5.5

Via the source data dialog set the data for LeaderLines series to be
XValues: =I2:I24
YValues: =J2:J24

Format LeaderLines series to have no marker a line style of choice.

Format datalabels series to have no marker or line style.
You need to apply data labels and link those to the range G2:G9. Quickest
way to do this is with this free add-in.
http://www.appspro.com/Utilities/ChartLabeler.htm

You should now have a stacked column chart with data labels to the right of
each column centered within each point.
By adjusting the datalabel and leaderlines x any positions you can place the
labels where ever you linke within the plotarea.
You can even use formula to calculate the positions so you have dynamic
positioning.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"suejay" wrote in message
...
Hi Andy-
Thanks for your suggestion - yes, we are forced to manually move the data
labels because the charts need to be small enough to fit on a PowerPoint
slide. How would I fake the leader lines with a dummy xy scatter series?
It's worth a try!

"Andy Pope" wrote:

Hi,

Pie charts are the only ones with built-in leader lines.
Stack column charts only have the option of positioning the labels Top,
Middle or bottom of column.

Are you manually moving the data labels?

I guess, as long as the chart is not 3d, you could fake the leader lines
using dummy xy scatter series.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"suejay" wrote in message
...
Help! Is there any way to use automatic leader lines to the data
labels
with
stacked column charts???
Thanks!

Hi John-
See my reply to Andy Pope - his suggestion works, but the challenge is still
posed when we have tiny columns in the stack. Did you have an alternative
chart style in mind?
Any suggestions are appreciate!

"Jon Peltier" wrote:

Instead of relying on leader lines, is there another way to identify the
items in the chart? Or perhaps is there a different way to lay out your
chart so the labeling is less critical to the display of information?

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


"suejay" wrote in message
...
Help! Is there any way to use automatic leader lines to the data labels
with
stacked column charts???
Thanks!

Hi,

Maybe breaking the Y axis would help.
http://www.andypope.info/charts/brokencolumn.htm

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"suejay" wrote in message
...
Thanks, Andy - it worked - it still poses a challenge because 3 of the
stacked columns are really tiny. I appreciate all your help!
Sue

"Andy Pope" wrote:

Create a stacked column chart on the range A1:C5 , where that range
contains
the following data
Note: empty is used to denote actual empty cell if included it will be
treated as zero.

empty Data1 Data2
a 1 2
b 2 3
c 3 4
d 4 3

Use the Source data dialog to add 2 more series called DataLabels and
LeaderLines
Select the Datalabels series and change to xy scatter chart type.
Repeat for Leaderlines series

Add the following data to range E1:G9
X Y Label
1.5 0.5 a1
2.5 1 b1
3.5 1.5 c1
4.5 2 d1
1.5 2 a2
2.5 3.5 b2
3.5 5 c2
4.5 5.5 d2

Via the source data dialog set the range for the Datalabels series to be
XValues: =E2:E9
YValues: =F2:F9

Add the following data to range I1:J24
X Y
1.5 0.5
1 0.5
empty empty
2.5 1
2 1
empty empty
3.5 1.5
3 1.5
empty empty
4.5 2
4 2
empty empty
1.5 2
1 2
empty empty
2.5 3.5
2 3.5
empty empty
3.5 5
3 5
empty empty
4.5 5.5
4 5.5

Via the source data dialog set the data for LeaderLines series to be
XValues: =I2:I24
YValues: =J2:J24

Format LeaderLines series to have no marker a line style of choice.

Format datalabels series to have no marker or line style.
You need to apply data labels and link those to the range G2:G9. Quickest
way to do this is with this free add-in.
http://www.appspro.com/Utilities/ChartLabeler.htm

You should now have a stacked column chart with data labels to the right
of
each column centered within each point.
By adjusting the datalabel and leaderlines x any positions you can place
the
labels where ever you linke within the plotarea.
You can even use formula to calculate the positions so you have dynamic
positioning.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"suejay" wrote in message
...
Hi Andy-
Thanks for your suggestion - yes, we are forced to manually move the
data
labels because the charts need to be small enough to fit on a
PowerPoint
slide. How would I fake the leader lines with a dummy xy scatter
series?
It's worth a try!

"Andy Pope" wrote:

Hi,

Pie charts are the only ones with built-in leader lines.
Stack column charts only have the option of positioning the labels
Top,
Middle or bottom of column.

Are you manually moving the data labels?

I guess, as long as the chart is not 3d, you could fake the leader
lines
using dummy xy scatter series.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"suejay" wrote in message
...
Help! Is there any way to use automatic leader lines to the data
labels
with
stacked column charts???
Thanks!

I don't know what the data looks like. In general, though, stacked charts
are less effective than they seem. You can get a good comparison of the
elements in the bottom series, but the upper series have irregular
baselines, so they are more difficult to compare, and comparison of elements
in different series is also very difficult.

Leader lines in general only add to chart junk. If they are needed, it
usually means the chart is too cluttered without them. The default
formatting of column and bar charts can be cleaned up. Remove the borders
around the bars, and let them be distinguished by their fill colors. You're
removing the chart junk of the borders, which can occupy critical pixels and
block the display of small elements.

If you don't have too many series, a clustered column chart might be better.
If you want to show fraction of the whole, then plot percentages instead of
values. If the category scale consists of dates, then a line chart is
better, as it conveys the sense of time.

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


"suejay" wrote in message
...
Hi John-
See my reply to Andy Pope - his suggestion works, but the challenge is
still
posed when we have tiny columns in the stack. Did you have an alternative
chart style in mind?
Any suggestions are appreciate!

"Jon Peltier" wrote:

Instead of relying on leader lines, is there another way to identify the
items in the chart? Or perhaps is there a different way to lay out your
chart so the labeling is less critical to the display of information?

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


"suejay" wrote in message
...
Help! Is there any way to use automatic leader lines to the data
labels
with
stacked column charts???
Thanks!