Excel Test - Create trend line from data set

[ickelly]

I am stumped by a question on a potential employer's Excel test. The objective is to create a graph showing each particular product's percentage of total month's sales, for a 12 month period. The data set is thousands of lines, each showing sales for each month for a particular product in a specific region (sample below). I started by creating a pivot table of the data set, that totaled sales for each product, by month. This was fairly straightforward. The trouble came when I wanted to create the graph with the trend line showing the percentage of net sales for the month that each product contributed. The instructions were to create one chart with trend lines showing each Product's performance over time, with vertical axis labeled as percentage of total sales, and the horizontal axis showing months (e.g. Month 1, 2, 3…etc). The lines are to be stacked and color coded with a legend identifying each line and the Product it represents. I tried adding a calculated field to the pivot table, but wasn't able to get the percentage of total sales. The original data set is in the format of the example below. Any ideas/help is greatly appreciated. - Thanks.

[TABLE="class: grid, width: 1000, align: left"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Region[/TD]
[TD]Month 1[/TD]
[TD]Month 2[/TD]
[TD]Month 3[/TD]
[TD]Month 4[/TD]
[TD]Month 5[/TD]
[TD]Month 6[/TD]
[TD]Month 7[/TD]
[TD]Month 8[/TD]
[TD]Month 9[/TD]
[TD]Month 10[/TD]
[TD]Month 11[/TD]
[TD]Month 12[/TD]
[/TR]
[TR]
[TD]Prod 1[/TD]
[TD]NE[/TD]
[TD]456[/TD]
[TD]678[/TD]
[TD]etc..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prod 1[/TD]
[TD]NW[/TD]
[TD]567[/TD]
[TD]453[/TD]
[TD]etc..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prod 2[/TD]
[TD]NE[/TD]
[TD]77[/TD]
[TD]56[/TD]
[TD]etc..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prod 2[/TD]
[TD]SE[/TD]
[TD]565[/TD]
[TD]345[/TD]
[TD]etc..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prod 2[/TD]
[TD]SW[/TD]
[TD]567[/TD]
[TD]356[/TD]
[TD]etc..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prod 3[/TD]
[TD]NW[/TD]
[TD]567[/TD]
[TD]36[/TD]
[TD]etc..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prod 3[/TD]
[TD]SW[/TD]
[TD]89[/TD]
[TD]356[/TD]
[TD]etc..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prod 4[/TD]
[TD]NE[/TD]
[TD]456[/TD]
[TD]34[/TD]
[TD]etc..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prod 4[/TD]
[TD]MW[/TD]
[TD]67[/TD]
[TD]34[/TD]
[TD]etc..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[eliW]

Hi ickelly,
In your thread you wrote :

I tried adding a calculated field to the pivot table, but wasn't able to get the percentage of total sales.

here is the solution (which I hope will give you a good start).
The "sum of amount" in the pivot table can be changed by Value Field Settings to show values as % of Row Total rather than plain Sum, as shown in the attached sample.
All the best,
Eli

Excel 2010
[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td=bgcolor:#FFFF00]

prod​

[/td][td=bgcolor:#FFFF00]

region​

[/td][td=bgcolor:#FFFF00]

month​

[/td][td=bgcolor:#FFFF00]

amount​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m1​

[/td][td=bgcolor:#FFFF00]

12​

[/td][td][/td][td=bgcolor:#DCE6F1]Sum of amount[/td][td=bgcolor:#DCE6F1]month[/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m1​

[/td][td=bgcolor:#FFFF00]

24​

[/td][td][/td][td=bgcolor:#DCE6F1]prod[/td][td=bgcolor:#DCE6F1]m1[/td][td=bgcolor:#DCE6F1]m2[/td][td=bgcolor:#DCE6F1]m3[/td][td=bgcolor:#DCE6F1]m4[/td][td=bgcolor:#DCE6F1]Grand Total[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m1​

[/td][td=bgcolor:#FFFF00]

50​

[/td][td][/td][td]p1[/td][td]

106​

[/td][td]

107​

[/td][td]

140​

[/td][td]

127​

[/td][td]

480​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m1​

[/td][td=bgcolor:#FFFF00]

20​

[/td][td][/td][td]p2[/td][td]

100​

[/td][td]

116​

[/td][td]

97​

[/td][td]

133​

[/td][td]

446​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m2​

[/td][td=bgcolor:#FFFF00]

11​

[/td][td][/td][td=bgcolor:#DCE6F1]Grand Total[/td][td=bgcolor:#DCE6F1]

206​

[/td][td=bgcolor:#DCE6F1]

223​

[/td][td=bgcolor:#DCE6F1]

237​

[/td][td=bgcolor:#DCE6F1]

260​

[/td][td=bgcolor:#DCE6F1]

926​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m2​

[/td][td=bgcolor:#FFFF00]

23​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m2​

[/td][td=bgcolor:#FFFF00]

32​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m2​

[/td][td=bgcolor:#FFFF00]

41​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m1​

[/td][td=bgcolor:#FFFF00]

20​

[/td][td][/td][td=bgcolor:#DCE6F1]Sum of amount[/td][td=bgcolor:#DCE6F1]month[/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1][/td][td=bgcolor:#DCE6F1][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m1​

[/td][td=bgcolor:#FFFF00]

51​

[/td][td][/td][td=bgcolor:#DCE6F1]prod[/td][td=bgcolor:#DCE6F1]m1[/td][td=bgcolor:#DCE6F1]m2[/td][td=bgcolor:#DCE6F1]m3[/td][td=bgcolor:#DCE6F1]m4[/td][td=bgcolor:#DCE6F1]Grand Total[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m1​

[/td][td=bgcolor:#FFFF00]

22​

[/td][td][/td][td]p1[/td][td]

22.08%​

[/td][td]

22.29%​

[/td][td]

29.17%​

[/td][td]

26.46%​

[/td][td]

100.00%​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m1​

[/td][td=bgcolor:#FFFF00]

7​

[/td][td][/td][td]p2[/td][td]

22.42%​

[/td][td]

26.01%​

[/td][td]

21.75%​

[/td][td]

29.82%​

[/td][td]

100.00%​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m2​

[/td][td=bgcolor:#FFFF00]

64​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

15​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m2​

[/td][td=bgcolor:#FFFF00]

20​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

16​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m2​

[/td][td=bgcolor:#FFFF00]

20​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

17​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m2​

[/td][td=bgcolor:#FFFF00]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

18​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m3​

[/td][td=bgcolor:#FFFF00]

30​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

19​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m3​

[/td][td=bgcolor:#FFFF00]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

20​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m3​

[/td][td=bgcolor:#FFFF00]

54​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

21​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m3​

[/td][td=bgcolor:#FFFF00]

42​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

22​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m4​

[/td][td=bgcolor:#FFFF00]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

23​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m4​

[/td][td=bgcolor:#FFFF00]

52​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

24​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m4​

[/td][td=bgcolor:#FFFF00]

24​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

25​

[/td][td=bgcolor:#FFFF00]

p1​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m4​

[/td][td=bgcolor:#FFFF00]

36​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

26​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m3​

[/td][td=bgcolor:#FFFF00]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

27​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m3​

[/td][td=bgcolor:#FFFF00]

40​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

28​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m3​

[/td][td=bgcolor:#FFFF00]

21​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

29​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m3​

[/td][td=bgcolor:#FFFF00]

22​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

30​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m4​

[/td][td=bgcolor:#FFFF00]

31​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

31​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

ne​

[/td][td=bgcolor:#FFFF00]

m4​

[/td][td=bgcolor:#FFFF00]

40​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

32​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m4​

[/td][td=bgcolor:#FFFF00]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

33​

[/td][td=bgcolor:#FFFF00]

p2​

[/td][td=bgcolor:#FFFF00]

nw​

[/td][td=bgcolor:#FFFF00]

m4​

[/td][td=bgcolor:#FFFF00]

54​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[mole999]

you can select a dataset on the chart and then add a trend directly from the right click menu (there are a number to choose from, and you can change the colour also directly)