superimposing bar chart on stacked bar chart

**frigyes.hausz** · 02-07-2018, 11:05 AM

Hi,

I am using MS Excel 2013.

Is there any way to combine a clustered bar chart with a stacked bar chart in excel, where the clustered bars are on the primary axis and the stacked bar is on the secondary axis and the two types of bars are superimposed on each other?
I attach a .png, to see what I mean (it is not in english, but I guess it is enough to clarify what I have in mind).
barchart_superimposed_stackedchart.png
I've already explored a number of articles on the topic (e.g. this: https://alesandrab.wordpress.com/201...arts-in-excel/) but these are not what I am looking for (as I don't want to cluster two stacked bar chart, but to superimpose the bar chart on the stacked chart)
In the attached file I just superimposed two separate charts with the same axis proportions (one clustered bar chart and one stacked bar chart) to create the result, but maybe someone has a better idea.

Thanks very much in advance!
Frici

**xlnitwit** · 02-07-2018, 11:24 AM

Hi,

You need only plot the last two series on the secondary axis and then change the chart type for them to a clustered rather than stacked bar. The rest is merely cosmetics such as decreasing the gap width on the primary axis series to make them plot wider than the clustered columns.

**MrShorty** · 02-07-2018, 11:29 AM

Here's what I did (basically a combination clustered bar, stacked bar with the clustered bar series on the secondary, if you already know how to do that):

1) Fake some data (two columns for category data, then 4 columns of random numbers -- I assume your data is similarly arranged in the spreadsheet).
2) Select table -> Insert -> Stacked bar chart.
3) Select series 1 -> Format series -> assign to secondary axis -> Make gap width smaller -> (Fill -> Solid -> transparency ~50%)
3a) Repeat step 3 for series 2.
4) Select either series 1 or series 2 -> Change chart type -> clustered bar.

That should be really close to your picture.