stacked chart percentage

[LukasPij]

[FONT=&quot]Heey guys,

[/FONT]
[FONT=&quot]Does anyone have some experience on the problem below? I am having the following problems when trying to create a stacked chart. I would like to make adjustment in the chart below.

[/FONT]
[FONT=&quot]Since I would like to make a similar chart with different data. For this example, assume the y-axis is different (e.g. % total revenue generated by selling televisions) and that the numbers in the chart stay the same.

I would like to adjust the chart in a way so that I can create a stacked chart, for example i would like to depict in the chart:
1999: 10 % of total revenue generated by selling televisions, of this 10%:
- 40 % plasma tv
- 30 % amoled tv
- 30 % led tv


2000: 11% of total revenue generated by selling televisions, of this 11%:
- 60 % plasma tv
- 20 % amoled tv
- 20 % led tv
[/FONT]

 

[thisoldman]

I find it's difficult to make comparisons using stacked column charts but if you want to do it, here's one way. I've just plugged in some dummy numbers for the known values.

I extended the data beyond what is posted here. The known values are in columns A through F. Values in columns H through L are calculated from these known values.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TH][/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]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]% of Total TV[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Year
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Total Rev[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Total TV[/TD]
[TD="align: right"]Plasma[/TD]
[TD="align: right"]AMOLED[/TD]
[TD="align: right"]LED[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]All TV[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Other[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Plasma[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]AMOLED[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]LED[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]1999
[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]56%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 1,200[/TD]
[TD="align: right"] 10,800[/TD]
[TD="align: right"] 397[/TD]
[TD="align: right"] 132[/TD]
[TD="align: right"] 671[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]2000
[/TD]
[TD="align: right"] 13,000[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]44%[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]43%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 1,430[/TD]
[TD="align: right"] 11,570[/TD]
[TD="align: right"] 631[/TD]
[TD="align: right"] 186[/TD]
[TD="align: right"] 613[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]2001
[/TD]
[TD="align: right"] 14,000[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]49%[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 1,680[/TD]
[TD="align: right"] 12,320[/TD]
[TD="align: right"] 818[/TD]
[TD="align: right"] 218[/TD]
[TD="align: right"] 643[/TD]
[/TR]
</tbody>[/TABLE]

Data
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH]H3[/TH]
[TD="align: left"]=B3*C3[/TD]
[/TR]
[TR]
[TH]I3[/TH]
[TD="align: left"]=B3 - H3[/TD]
[/TR]
[TR]
[TH]J3[/TH]
[TD="align: left"]=D3*H3[/TD]
[/TR]
[TR]
[TH]K3[/TH]
[TD="align: left"]=H3*E3[/TD]
[/TR]
[TR]
[TH]L3[/TH]
[TD="align: left"]=F3*H3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

For the first chart, select the data in columns A and I through L. Then on the ribbon, select Insert >> Column Chart >> Stacked Column.

For the second chart, select the data in columns A and J through L. Insert another stacked column chart.