clustered stacked column pivot chart broken down by the data in each attribute

I had to do a bit of a two-step with this. The data had to be converted into numerical data and unfortunately is not organized for a pivot table to do it.

First, I converted the data into a table and named it Table_Responses. Tables have a lot of advantages, one of them being that the "know" how big they are so any pivot tables, formulas, charts, etc. that reference them always look at the exact right amount of data. There is no need to guess at how many rows to include.

I made a second table using COUNTIF formulas. When I built the chart on this table, I got a "transposed" stacked bar chart. The X-axis had definite, maybe and no way, and the bars showed the number of questions that had that answer. If you right click on the chart and select the select data option at the top of the dialog box there is an option to transpose the X and Y's.

In this case, I enumerated the Responses and the Attributes manually. If the responses and attribute data are fixed, this is a viable option. If they can vary, then VB code is required to "build" the table. However, as pointed out above, the chart will react (add values and series) to follow the table whatever content the table winds up with.

My first observation is that your source data is not arranged in the best way for a pivot table. Getting a better pivot table should allow you to get the stacked column chart you want. You might review this recent discussion and associated links: https://www.excelforum.com/excel-new...ta-layout.html

Here's what I did:
1) I don't have power pivot or Get and Transform like others do, which should make the "unpivot" step here much easier. I manually unpivoted the data by moving the data for each attribute type down beneath the others to make a long list:

Please Login or Register  to view this content.

If Q1 stands for quarter -- suggesting that there is another variable (date/time/quarter) -- then separate that out into another column.
2) Build pivot table from the new source table with:
2a) attribute type as row label
2b) response as column label
2c) count of response as value field.
3) Insert stacked column chart based on pivot table.

Is that what you are trying to do?

This is a much more flexible response anyway.

As MrShorty pointed out, your data isn't in a format suitable for pivot table analysis. This program puts the data into that format.