Stacked Column Chart with % increases

[crosser]

I have several of these charts with 2-4 series in them.
This could be to compare sales of 2-4 different types of products in the current MTD vs prior MTD
I want to add a series line in between them, but also to add labels that show the % change.

Initially I did this by first adding a series line. Then adding another set of values inbetween with no line, just plotting the % change values on a second axis. The problem with doing this in the stacked columns however is that every time the % changes I have to reorganize the location of the labels to match the corresponding series. Example the 2nd series goes from 10% to 50%. It would then put the label higher then the 3rd and 4th series in the stacked column so I have to move it manually.

I know there is a way to do this, and I think it involved specifiying both x and y values however I couldn't find any instructions on how it was done.

Any ideas?

 

[crosser]

Bump

Is anyone able to help?

 

[Gerald Higgins]

I'm not sure exactly what you mean, but I think I know.

If you want to control the positioning of data labels, you can do this by creating a chart series that exists only to show the data labels and nothing else.
You'll need some formula to generate the data values, perhaps linked to your existing data that's driving the stacked bars.
Then add a series for this data, change the chart type for that single series to LINE, turn data labels on for that series, and format the line to have no colour and no symbol, so that it is invisible except for the labels.

If you're struggling with this, post back with more details on exactly what you want to do and I (or someone else) may be able to give more precise instructions.
Then add the series

 

[crosser]

Hi Gerald,
Thanks for the reply. I've done that before with a single series but in this case I am dealing with a stacked column with several series so the data labels don't match each correctly. Here is a pic of the graph I am working with.

Any other suggestions? Another example used both x and y values to plot the labels but that appeared pretty complicated.

 

[Gerald Higgins]

OK so in your example, which specific label(s) do you think is/are in the wrong place, and where should it be ?

 

[Steve=True]

Perhaps the issue is that they are on the 2nd axis.

No reason they can't be on the same axis.

Steve=True
www.exceldashboardtemplates.com

 

[crosser]

As shown in the picture there will only be one % value that lines up correctly - for Eastern - since it is the first set of values. All the others will be lower since they are on a second axis as a line graph.

If they are on the same axis then I would have to put those series (%) as a Stacked column in order for the % to line up with the correct Region (ex Western, Central). If I do that then the series line won't work because I will have a full set of values in between the main 2 columns.

However i can't do another stacked column (and make it invisible) on a second axis.

I'm sorry I am having trouble explaining it. I swear there is a way using x and y values since I have seen it myself but I don't understand the logic. I believe it involves calculating the average between each series and plots the % change value using x/y values.

 

[crosser]

OK so in your example, which specific label(s) do you think is/are in the wrong place, and where should it be ?

All of the % labels should be automatically placed in the middle or slightly below the series line.

 

[Steve=True]

yes, you can do this with an XY series.

1) add a a new series to your graph (primary axis is okay) any value will work as you will have to change this later

2) change the series you just added to an XY series with points

3) edit the series as it may not have both an X and a Y since it was set up as a stacked column to begin with. X=1.5 Y=% difference

4) add label to that point


Hope this helped!

Steve=True
www.exceldashboardtemplates.com

 

[Andy Pope]

An alternative to the xy scatter is to use 4 additional series plotted as stacked area. The first 3 series can use the same values as the stacked columns. If you use the %Change calculation for the series name you can apply Series Name data labels to the stacked area series and it will appear inbetween the columns and correctly positioned.
The fourth series can use a suitable value, say 100, to position the %change for totals.

Another extra series based on the Total values and plot as column on the secondary axis.
Delete the secondary value axis to force the new series to use the primary axis. Apply value data labels and position outside end.