Line Chart with % Change included

[Chrazyman]

Good morning, I would like to show the change of one customer's consumption from 3 suppliers over 5 years.
The top line is the total, with the bottom 3 as the share from each supplier. I am trying to add the % change between the years as well. Since the data is not static, I would like it to change and move position as the numbers change if that is possible. I've included a sample data set, but had to manually enter the percentage change. I would like those percentages to show for each line, not just the customer (top) line. This is roughly how I would like it to look. Thanks for your help.

[MrShorty]

Your profile shows you are using Excel 2010. Is that still true? Data label options changed in 2013, so it could be important to know if your version is correct.

Here's how I would do it:

1) I do not like the "scattered" way you have the data arranged. I would arrange the data so that the values are in one contiguous block and then compute the percentages in one contiguous block. If you prefer this table to present the data, then make a copy of the table where you can rearrange the values:

Please Login or Register  to view this content.

2) Insert the line chart based on the first 6 columns. If Excel misinterprets the intended data series, fix things in the Select Data dialog.
3) Add duplicates of all 4 data series, to make a total of 8 data series.
4) For each "pair" of data series, add appropriate data labels to the data series.
4a) For the series where you want to show the actual value, add the data label and format to show the Y value.
4b) For the series where you want to show the % change, add a data label and format to show the values in the %change columns. Note that Excel 2010 does not include this functionality, so you need to either create the functionality yourself (in VBA) or use an add-in like Rob Bovey's free XY chart labeler add-in (appspro.com ).

That's how I would do it. Will something like that work for you?

[Chrazyman]

I'm using Windows 365 at work, I'll update that. I'm open to a bar chart and have updated the sheet. How do I have the percentage of each supplier as a part of the whole customer show instead of their actual volume number?

[JeteMc]

How do I have the percentage of each supplier as a part of the whole customer show instead of their actual volume number?

To accomplish this it would seem that the data selection for each supplier should be changed to get the percentages from columns C, E, G, I, K and M
Let us know if you have any questions.