If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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! |
Thread Tools | |
Display Modes | |
|
|