Stacked Bar Chart with multiple bars for each month

RogerC

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

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

RogerC

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

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

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

RogerC

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

Well-known Member
Joined
Mar 25, 2002
Messages
529

ADVERTISEMENT

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
 

aggy

New Member
Joined
May 22, 2010
Messages
2
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

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
Top