Stacked Bar Chart with multiple bars for each month

[RogerC]

I'm having trouble sturcturing my data to achieve what I need in a stacked bar chart. Basically, I need a chart with the months of the year for the category axis. For each month I have 2 item types (two bars) to be plotted as values on the Y axis. For for each item type there are multiple sub-types (the stacks for the bars). Example; For each month I have red and blue items that are sold in different regions. The red and blue items need to be stacked in a seperate bar with each showing their region values. The data for one month might look like this:

January
Red: 2 North, 1 South, 4 East, 1 West
Blue: 3 North, 2 South, 1 West

Can someone help me understand how to layout the data?

 

[RogerC]

Here is a sample of what my data would look like. For each month I need one stacked bar that contains 'Red & Blue' (stacked together) and 'Shipments' as a single bar. When I try to add 'Shipments' as a new series to the chart, Excel wants to make one single stacked bar with 'Red, Blue & Shipments' all combined. Does anyone know how I can make a stacked bar chart with two bars for each month?

Book1
	A	B	C	D
2		Red	Blue	Shipments
3	Jan	70	90	350
4	Feb	100	126	400
5	Mar	150	150	435
6	Apr	120	140	259
7	May	100	200	380
8	Jun	100	230	512
9	Jul	50	100	300
10	Aug	120	90	269
11	Sep	200	123	422
12	Oct	75	90	234
13	Nov	30	234	470
14	Dec	67	125	325
Sheet1

 

[fairwinds]

Hi,

The easiest way would be to move the "shipment" data to secondary axis and then adjust gap and overlap.

 

[RogerC]

fairwinds - Unfortunately I can't do that. I did not give you all of the information I should have (oops). I actually have another column of data that is the percentage of Red and Blue combined compared to Shipments. I will be graphing the Percent on the secondary axis so I won't be able to graph Shipments there.

I was able to create a chart with one stacked and one solid bar for each month by following this procedure:
1) Create a Standard Type Clustered-column chart using Red, Blue and Shipments.
2) Right click on the Shipments series bar and choose Chart Type... Custome Types, Line-Column.
4) Right click on the Shipments series (now a line) and choose Chart Type... Standard Types, Column, Clustered-Column.

This gives me a stacked-bar (Red and Blue) and a single bar (Shipments) in each month. However, the Shipments bar is directly on top of the stacked-bar and no matter what I try to do with Gap Width and Overlap for any of the bars I cannot get them to display side-by-side. Argh!

Does anyone know of a way to make the stacked-bar and the solid bar to display side-by-side?

 

[RogerC]

Sorry, I missed one step in my earlier post. Here is how I created a chart with one stacked-series bar and one single-series bar for each month:

1) Create a Standard Type Clustered-column chart using Red, Blue and Shipments.
2) Right click on the Shipments series bar and choose Chart Type... Custom Types, Line-Column.
3) Right click on the Red series bar and choose Chart Type... Standard Types, Column, Stacked-column.
4) Right click on the Shipments series (now a line) and choose Chart Type... Standard Types, Column, Clustered-Column.

This gives me the two bars I need, but I cannot move the bars so they are beside each other.

Any ideas?



This is what my total data actually is.

Book1
	B	C	D	E	F
33		Red	Blue	Shipments	Percent
34	Jan	70	90	350	46%
35	Feb	100	126	400	57%
36	Mar	150	150	435	69%
37	Apr	120	140	259	100%
38	May	100	200	380	79%
39	Jun	100	230	512	64%
40	Jul	50	100	300	50%
41	Aug	120	90	269	78%
42	Sep	200	123	422	77%
43	Oct	75	90	234	71%
44	Nov	30	234	470	56%
45	Dec	67	125	325	59%
Sheet1

 

[aggy]

I have a similar issue, I want to chart my actual & budget sales on the one graph, by month & by country. I want the budget to be a line chart and actual sales to be column. How do I do this?

<table style="border-collapse: collapse; width: 215pt;" border="0" cellpadding="0" cellspacing="0" width="287"><col style="width: 77pt;" width="103"> <col style="width: 56pt;" width="75"> <col style="width: 48pt;" width="64"> <col style="width: 34pt;" width="45"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 77pt;" height="20" width="103">Actual Sales</td> <td class="xl64" style="width: 56pt;" width="75">Jan</td> <td class="xl64" style="width: 48pt;" width="64">Feb</td> <td class="xl64" style="width: 34pt;" width="45">March</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">US</td> <td class="xl63">100</td> <td class="xl63">200</td> <td class="xl63">300</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">UK</td> <td class="xl63">400</td> <td class="xl63">500</td> <td class="xl63">600</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">Japan</td> <td class="xl63">100</td> <td class="xl63">500</td> <td class="xl63">700</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">Africa</td> <td class="xl63">350</td> <td class="xl63">450</td> <td class="xl63">900</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Budget Sales</td> <td class="xl64">Jan</td> <td class="xl64">Feb</td> <td class="xl64">March</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">US</td> <td class="xl63">355</td> <td class="xl63">400</td> <td class="xl63">500</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">UK</td> <td class="xl63">700</td> <td class="xl63">800</td> <td class="xl63">900</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">Japan</td> <td class="xl63">150</td> <td class="xl63">500</td> <td class="xl63">450</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">Africa</td> <td class="xl63">300</td> <td class="xl63">500</td> <td class="xl63">100</td> </tr> </tbody></table>

 

[tipperjoe]

Anyone ? I have a similar problem