Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 7
Default Leader Lines - Stacked Column Charts

Help! Is there any way to use automatic leader lines to the data labels with
stacked column charts???
Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default Leader Lines - Stacked Column Charts

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!


  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 7
Default Leader Lines - Stacked Column Charts

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!


  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default Leader Lines - Stacked Column Charts

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!



  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default Leader Lines - Stacked Column Charts

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!





  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 7
Default Leader Lines - Stacked Column Charts

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!


  #7   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 7
Default Leader Lines - Stacked Column Charts

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!




  #8   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default Leader Lines - Stacked Column Charts

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!



  #9   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default Leader Lines - Stacked Column Charts

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!






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Leader Lines in charts other than pies? Eric Charts and Charting in Excel 1 February 10th 07 04:16 PM
How can I re-route the leader lines in a Pie chart? John Casper Charts and Charting in Excel 2 January 10th 07 02:57 PM
Combine Clustered Column Bar charts and Stacked column bar charts Richard Charts and Charting in Excel 1 October 26th 06 02:44 AM
Leader Lines on Pie charts billy_bags Charts and Charting in Excel 1 July 14th 06 10:00 PM
Leader Lines Connecting Chart to Box TOMB Excel Discussion (Misc queries) 1 May 7th 05 06:02 PM


All times are GMT +1. The time now is 06:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"