How to Superimpose 2 Stacked Column Charts

[monhue2]

[h=3]I have two stacked column charts (with no gaps for gap width between the columns) that have identical X- and Y-axes with same intervals. One graph is displayed in red (phase 1), while the other graph is displayed in blue (phase 2). When the graphs are superimposed, there will be some areas that are overlapped. How do I superimpose these two graphs as well as still show both the red and blue in intersecting areas? The following are two sample data set for the two column charts. The overlapping will occur in Apr-Jul 2015. I don't think I can combine the information to one graph because when I do that, one of the graphs is stacked on top of the other graph. I want both graphs to start on the X-axis line, so then I can see the overlap between the two colors.


PHASE 1[/h][TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Project A[/TD]
[TD]Project B[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Jul[/TD]
[TD]150[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Aug[/TD]
[TD]150[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sep[/TD]
[TD]150[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oct[/TD]
[TD]150[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Nov[/TD]
[TD]150[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Dec[/TD]
[TD]150[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Jan[/TD]
[TD]150[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Feb[/TD]
[TD]150[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Mar[/TD]
[TD]150[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apr[/TD]
[TD]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]May[/TD]
[TD]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jun[/TD]
[TD]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jul[/TD]
[TD]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Aug[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sep[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oct[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Nov[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Dec[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Phase 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Project A[/TD]
[TD]Project B[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Jul[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Aug[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sep[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oct[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Nov[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Dec[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Jan[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Feb[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Mar[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apr[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]May[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jun[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jul[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Aug[/TD]
[TD]300[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sep[/TD]
[TD]300[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oct[/TD]
[TD]300[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Nov[/TD]
[TD]300[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Dec[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlphaFrog]

One way to do this is create a data table that combines the two Phase data sets and calculates the overlap as another series like below. Then chart the recalculated table (yellow cells).

<br />

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1			Recalculated						Raw Data
2			Phase 1		Overlap	Phase2			Phase 1		Phase2
3	Year	Month	Project A	Project B	Project A-B	Project A	Project B		Project A	Project B	Project A	Project B
4	2014	Jul	150	105	0	0	0		150	105	0	0
5		Aug	150	105	0	0	0		150	105	0	0
6		Sep	150	105	0	0	0		150	105	0	0
7		Oct	150	105	0	0	0		150	105	0	0
8		Nov	150	105	0	0	0		150	105	0	0
9		Dec	150	105	0	0	0		150	105	0	0
10	2015	Jan	150	105	0	0	0		150	105	0	0
11		Feb	150	105	0	0	0		150	105	0	0
12		Mar	150	105	0	0	0		150	105	0	0
13		Apr	0	0	150	0	350		150	0	0	500
14		May	0	0	150	0	350		150	0	0	500
15		Jun	0	0	150	0	350		150	0	0	500
16		Jul	0	0	150	0	350		150	0	0	500
17		Aug	0	0	0	300	500		0	0	300	500
18		Sep	0	0	0	300	500		0	0	300	500
19		Oct	0	0	0	300	500		0	0	300	500
20		Nov	0	0	0	300	500		0	0	300	500
21		Dec	0	0	0	300	0		0	0	300	0
Sheet1

Cell Formulas
Range	Formula
C4	=I4-E4
E4	=MIN(I4,L4)
G4	=L4-E4

 

[monhue2]

I think this way might get complicated when I have nearly 90 projects to plot in a graph. I was thinking that when you combine two charts together, the overlapping areas would look something like this where you can see both colors.:
Multiple integral - Wikipedia, the free encyclopedia

Is it possible to do that in excel?

 

[AlphaFrog]

I think this way might get complicated when I have nearly 90 projects to plot in a graph. I was thinking that when you combine two charts together, the overlapping areas would look something like this where you can see both colors.:

Is it possible to do that in excel?

No I don't think so.

 

[monhue2]

I learned that 3D graphs can actually layer, and you can adjust the transparency of one of the layers to see both colors. Is there a way to combine two chart types: '3-D Column' and 'Stacked Column in 3-D'? In my example, project B will stack on project A. But there will be two layers: one layer for Phase 1 and another layer for Phase 2.