Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sorry for the slightly confusing title but I am not 100% how to phrase this one. So I have an excel sheet that has some survey results. There are 3 questions which can be answered from 1-5. I.e. 5 strongly agree - 1 strongly disagree. The columns are Gender and the questions, the rows are each respondent and each cell on a row is the 1-5 value they chose.

gender  question1   question2   question3
Male       1            4          3
Female   2            1          3
Male       1            3          5
Female   4            3          4
Female   5            4          3
Male       5            5          2

What I want to do is create a pivot table that shows the results as follows:

question1      1     2    3     4     5
  Male         2     0    0     0     1
  Female       0     1    0     1     1

question2      1     2    3     4     5
  Male         0     0    1     1     1
  Female       1     0    1     1     0

etc...

I have been tried but can not seem to figure out how to get the columns to work this way. Is it even possible?

Thanks

share|improve this question

1 Answer 1

The data format is already in a pivot form which makes it difficult, if not impossible, to create a new pivot the way you would like.

You should arrange your input data completely unpivoted to begin with...

gender  question     answer
Male    question1    1
Female  question1    2
Male    question1    1
Female  question1    4
Female  question1    5
Male    question1    5
Male    question2    4
Female  question2    1
Male    question2    3
Female  question2    3
Female  question2    4
Male    question2    5
Male    question3    3
Female  question3    3
Male    question3    5
Female  question3    4
Female  question3    3
Male    question3    2

Then create your pivot table as follows:

Column labels:  answer
Row labels:     question, gender
Values:         Count of answer

If you already have a lot of existing data, you can find instructions to unpivot it here: Is it possible to “unpivot” or “reverse pivot” in Excel?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.