Stacked Bar Chart with multiple bars for each month

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
536
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?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
ABCD
2RedBlueShipments
3Jan7090350
4Feb100126400
5Mar150150435
6Apr120140259
7May100200380
8Jun100230512
9Jul50100300
10Aug12090269
11Sep200123422
12Oct7590234
13Nov30234470
14Dec67125325
Sheet1
 
Upvote 0
Hi,

The easiest way would be to move the "shipment" data to secondary axis and then adjust gap and overlap.
 
Upvote 0
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?
 
Upvote 0
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
BCDEF
33RedBlueShipmentsPercent
34Jan709035046%
35Feb10012640057%
36Mar15015043569%
37Apr120140259100%
38May10020038079%
39Jun10023051264%
40Jul5010030050%
41Aug1209026978%
42Sep20012342277%
43Oct759023471%
44Nov3023447056%
45Dec6712532559%
Sheet1
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,218,899
Messages
6,145,091
Members
450,590
Latest member
Naneng

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top