Stacked and Non-Stacked bars in same chart?

[meg2011]

Hey all,

I need to create a bar chart with the example info:

A 10
B 27
C 52
D 60
E 21

D and E need to be a stacked bar, while A-C need to be single bars. The data has to be in this order.

Can it be done?
Meg

 

[wigi]

You could trick Excel, by having a stacked bar chart and for A-C, add 0 for the "second" part (stacked). Visually, the effect is the same.

 

[Akashwani]

Hi Meg,

Welcome to MrExcel.

Can you rearrange your data?

Excel Workbook
	B	C	D
2	Data	Value 1	Value 2
3	A	10	0
4	B	27	0
5	C	52	0
6	D	0	60
7	E	0	21
Sheet2

Which version of Excel are you using?
If you can rearrange your data, in 2007 highlight your range of data, click insert and select a column chart, right click Values 2, click Change Series Chart Type, select your Bar Chart.

I hope that helps, good luck.

Ak

 

[meg2011]

okay good plan, but please explain how I tell Excel that A and A1 are one bar for each plan, B and B1 are a bar, and so on.

More detail:
<TABLE style="WIDTH: 249pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=333><COLGROUP><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4778" width=134><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=22><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 654" width=18><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=22><COL style="WIDTH: 13pt; mso-width-source: userset; mso-width-alt: 625" width=18><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=22><COL style="WIDTH: 13pt; mso-width-source: userset; mso-width-alt: 625" width=18><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1024" width=29><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=22><TBODY><TR style="HEIGHT: 31.2pt; mso-height-source: userset" height=42><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 101pt; HEIGHT: 31.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=42 width=134></TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 16pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68 width=22>A</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68 width=18>A1</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 16pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68 width=22>B</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 13pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68 width=18>B1</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 16pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68 width=22>C</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 13pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68 width=18>C1</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 11pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68 width=14>D</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 22pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68 width=29>D1</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 11pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68 width=14>E</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 16pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68 width=22>E1</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 15.6pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=21>PLAN1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>20</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>90</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>50</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>139</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>70</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 15.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=21>PLAN2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>22</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>74</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>70</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>104</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>50</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 15.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=21>PLAN3</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>22</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>96</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>40</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>104</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>50</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 15.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=21>PLAN4</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>30</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>99</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>60</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>124</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>50</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #ffffcc; HEIGHT: 15.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=21>PLAN5</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>30</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>84</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>60</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>124</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>50</TD></TR></TBODY></TABLE>

 

[Akashwani]

Hi Meg,

I'm sorry but I'm unable to create the chart you require with your data.

See if this video gives you some ideas....

http://www.youtube.com/user/ExcelIsFun#p/search/22/Ovz75rbqQAk

Good luck

Ak

 

[pgc01]

Hi Meg
Welcome to the board

Jon Peltier explains here how to create a Clustered-Stacked Column Chart:

http://peltiertech.com/WordPress/clustered-stacked-column-charts/