1

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

etc...

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

karel
  • 13,706
Jon
  • 111

1 Answers1

0

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?

Community
  • 1
Mike Fitzpatrick
  • 17,427