Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an Excel spreadsheet that has some survey results.There are 3 questions which can be answered on a scale of 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


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

share|improve this question

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


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.