Creating a Clustered Stacked Bar Chart

[henryjarend]

Hello,

I am trying to create a clustered stacked bar chart. I have done a google and it returned some results that would work if the data was formatted correctly and if you wanted to skew your data. However I have been trying to keep the data as true to the original as possible. I will attach some data to this post along with an image of the chart I am trying to create. On the attached workbook sheet one has the data I want to use and then the second sheet has the graph I want to create. The data for sheet two is too convoluted and would become too troublesome for data sets larger than about 5 or so. If you have any questions feel free to message me. clustered stacked bar chart.xlsx

[Steve N.]

I'm confused. It sounds like you need a duplicate of the chart on sheet2 but with sheet1 data? You can do this by simply changing the cell references from the chart in sheet2 to the data set in sheet1 - see attached.

If that's not what you're looking for, please provide more detail.

[henryjarend]

Sorry I reread what I had originally posted and it was rather confusing. I have updated the data and will attach it. I want to make the graph that is shown on sheet 2 but using the data strictly from sheet 1. The graph I have created on sheet 1 can be disregarded that was just me trying different scenarios to see if I could find a solution. clustered stacked bar chart.xlsx

[Steve N.]

Looks much like your first attachment... Are you sure this is the correct one? If so -

See where the data is in Sheet2? The second set, Category 6, 7, and 8 occupy cells F6 to H9. You'll need to put the sheet1 data in the same cells.

The attachment I sent has it done...

[henryjarend]

I think maybe I am wording it backwards. I want to create a graph that looks like the one shown on sheet 2 however I cannot move any of the data around on sheet 1. In other words the data needs to occupy the same cells no matter what. The only problem with how the data is organized on sheet 2 is that for any sets of data(for example if the number categories were to become large) the amount of effort to be put into rearranging the data would become more and more.

[Steve N.]

Ok, think I understand - hold on a minute...

[Steve N.]

This is more like your sample. Since the data sets share the same rows, they will default to the same color. You can change the individual colors for one set to you liking.

This help?