Automating good data labels on stacked area pivot

[HalpClueless]

I have a stacked area showing resource use across time. Each resource is categorized by:

Chemical ChemA, ChemB, ChemC etc. (values change by month)
Cost cheap/expensive (does not change by month per chemical)
Region AUS/EU/NA (does not change by month per chemical)

I slice/pivot the data so the viewer can elect to see only cheap chemicals, or only AUS chemicals, or only cheap AUS chemicals etc. as desired.

Example data and pivots below (I hope?):

[TABLE="width: 1245"]
<colgroup><col width="83" span="15" style="width:62pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 83"]Chemical[/TD]
[TD="class: xl65, width: 83"]Price[/TD]
[TD="class: xl65, width: 83"]Region[/TD]
[TD="class: xl66, width: 83, align: right"]Jan-15[/TD]
[TD="class: xl66, width: 83, align: right"]Feb-15[/TD]
[TD="class: xl66, width: 83, align: right"]Mar-15[/TD]
[TD="class: xl66, width: 83, align: right"]Apr-15[/TD]
[TD="class: xl66, width: 83, align: right"]May-15[/TD]
[TD="class: xl66, width: 83, align: right"]Jun-15[/TD]
[TD="class: xl66, width: 83, align: right"]Jul-15[/TD]
[TD="class: xl66, width: 83, align: right"]Aug-15[/TD]
[TD="class: xl66, width: 83, align: right"]Sep-15[/TD]
[TD="class: xl66, width: 83, align: right"]Oct-15[/TD]
[TD="class: xl66, width: 83, align: right"]Nov-15[/TD]
[TD="class: xl66, width: 83, align: right"]Dec-15[/TD]
[/TR]
[TR]
[TD]ChemA[/TD]
[TD]Cheap[/TD]
[TD]NA[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ChemB[/TD]
[TD]Cheap[/TD]
[TD]NA[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]ChemC[/TD]
[TD]Cheap[/TD]
[TD]EU[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]37.5[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]42.5[/TD]
[TD="align: right"]33.5[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
[TR]
[TD]ChemD[/TD]
[TD]Expensive[/TD]
[TD]EU[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ChemF[/TD]
[TD]Expensive[/TD]
[TD]EU[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]37.5[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]ChemG[/TD]
[TD]Expensive[/TD]
[TD]AUS[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]42.5[/TD]
[TD="align: right"]33.5[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]ChemH[/TD]
[TD]Expensive[/TD]
[TD]AUS[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]33.5[/TD]
[/TR]
[TR]
[TD]ChemI[/TD]
[TD]Expensive[/TD]
[TD]AUS[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]33.5[/TD]
[/TR]
</tbody>[/TABLE]

Anyway, I want to label the areas representing each chemical, so viewers can easily see which are which. I can add data labels one-by-one by selecting add data labels -> format data labels -> uncheck "Value" and check "Series Name". However, these labels all appear in the middle of the chart (over June 2015)...some series may be flat (0) at that month so the labels look awkward and are all bunched up, especially for a large number of different chemicals.

If I try to move the labels, Excel 2013 adds leader lines to where the axes cross - and they get re-added whenever I change the pivot filters.

Is there a better way to do this?

Thanks!

HalpClueless

 

[Wessie]

Hi Halp

I would recommend creating a style for your chart and saving that style. You can then select the style each time easily or even program a button or event to change the style when you need it to.
Therefore you can always let the chart look the way you want it to.

Let me know if this helps or if I misunderstood you.
Regards
R

 

[HalpClueless]

Hi Halp

I would recommend creating a style for your chart and saving that style. You can then select the style each time easily or even program a button or event to change the style when you need it to.
Therefore you can always let the chart look the way you want it to.

Let me know if this helps or if I misunderstood you.
Regards
R

Thanks for your reply Wessie! The data labels seem to be positioning themselves fine, though by default they appear in the middle of the chart (center of the timescale, with time on the x-axis), so I have to horizontally re-position them so they occupy some space covered by the area to which they pertain. Whenever I redefine the pivot parameters, Excel will remember my horizontal re-position (as well as recalculate what the vertical position should be) and it will move the label appropriately to be in the correct series. However, whenever it does this it adds leader lines, which get automatically re-added anytime I change the pivot filters. Chart styles do not seem to have any effect on leader lines, and deleting them one-at-a-time every time I change a pivot filter is not practical...

As a workaround: Does anyone know any way to make leader lines transparent/invisible by default?

 

[Wessie]

1. Click a chart that has data labels displayed.
   This displays the Chart Tools, adding the Design, Layout, and Format tabs.
   
2. On the Layout tab, in the Labels group, click Data Labels.
   
   
   
   
3. Click More Data Label Options.
   
4. In the Label Options category, under Label Contains, select or clear the Show Leader Lines check box to show or hide the leader lines.
   
   In programming:
   

       ActiveSheet.ChartObjects("Chart 1").Activate
       ActiveChart.SeriesCollection(1).DataLabels.Select
       ActiveChart.SeriesCollection(1).HasLeaderLines = False

   
   Hope this helps.
   Regards
   R