S
suejay
Help! Is there any way to use automatic leader lines to the data labels with
stacked column charts???
Thanks!
stacked column charts???
Thanks!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Andy Pope
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
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
S
suejay
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!
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!
A
Andy Pope
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
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
J
Jon Peltier
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
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
S
suejay
Thanks, Andy - it worked - it still poses a challenge because 3 of the
stacked columns are really tiny. I appreciate all your help!
Sue
stacked columns are really tiny. I appreciate all your help!
Sue
S
suejay
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!
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!
A
Andy Pope
Hi,
Maybe breaking the Y axis would help.
http://www.andypope.info/charts/brokencolumn.htm
Cheers
Andy
Maybe breaking the Y axis would help.
http://www.andypope.info/charts/brokencolumn.htm
Cheers
Andy
J
Jon Peltier
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
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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.