Survey Table

[zacksg1]

Hi,

I have data from a survey. It is in the form of a table with a few fields of demographic information (job location, job role, etc.) and then a large number of fields corresponding to particular survey questions where the response to each question is of the form "Strongly agree," "Agree," "Disagree," etc. for each row.

For example, a typical row would be of the form: MiddleSchool Teacher Agree Agree Disagree StronglyAgree ....

I would like to set up a pivot table where each question is a row, each column is one of the responses ("Strongly agree", ...) and each cell shows a count of the number of people who gave that particular response to the particular question.

I can do this manually using lots of countif formulas. However, I'd like to be doing some filtering, adding in slicers, and potentially categorizing by additional demographic categories. Thus, I'd like to use some sort of pivot table to make this easy. However, I've been struggling to set up the pivot table in this way--in particular I can pivot by any of the question columns to get the responses as the column headers, but I'm not sure how to get the other questions to "share these same column headings--if I pivot by other questions, I get multiple nested layers of the possible responses as column headers.

Let me know if you have any advice! I'm happy to use the internal excel pivot table and/or functionality of Power Pivot or Power Query.

Thanks!

 

[Steph77]

Just been playing around with a pivot table here and I see what you mean! My first instinct was "this sounds simple" and then I went and played with it...

It's hard to describe how I found to solve it, so bear with me here.

Say you have 6 questions and 3 additional categories of information, those being Income Level, Profession and Marital Status

Set up a line of cells detailing each of these other categories (so A15 is Income Level, B15 is Profession and C15 is Marital Status)

In the next row down, underneath each of these categories, set data validation for the column containing that category as a list of the entire data set plus a blank row at the bottom (note this will be better if there are only a set number of answers allowed and you can contain the list of possible answers somewhere separate from your data table - otherwise you'll get a huge long list in the drop-down containing multiple copies of each possible answer instead of a well-sorted list containing only one of each possible answer)

In the next row down have this formula: =IF(A16="","*",A16)

Now, set up a table of question numbers down the left side and answers across the top. In each cell look for something like:

=COUNTIFS(D$2:D$7,B18,A$2:A$7,A17,B$2:B$7,B17,C$2:C$7,D17)

As standard that table will give you a count of all the answers, however changing the drop downs above the table to look for certain categories you can limit the answers it's allowed to count.

Does that make sense?

If you PM me your email address I'll send you a copy of the sheet I just got a basic version working on so you can see what I did, might be easier than trying to interpret the above?

 

[zacksg1]

This is great thanks! Using dropdown menus to essentially act as filters is a good idea, particularly with adding in that * option for giving people the option not to select something in a particular category. This certainly solves the problem, but it seems like there's got to be a cleaner way to do this somehow.... It seems like this kind of analysis is something lots of people probably need to be doing pretty frequently!

 

[Steph77]

I think for the more specialised stuff there are a lot of specialist survey software packages out there that can do a lot of this more elegantly. A quick google will fid hundreds of results for that.

Hell I think it can probably be done more elegantly even in Excel, someone might even come along and provide you that more elegant solution here, but remember that Excel is a more general tool that was initially built for different work and so there may be some level of compromise in any solution within Excel itself.

A few years back I was doing a job that involved a lot of address manipulation from worldwide address lists and it surprised me how many tools are out there for Excel for that kind of work, but if I had wanted to really get serious about it it would have involved specialist software built for address manipulation rather than shoehorning Excel into a box it wasn't built for.