Forum Discussion
Pivot table issue
Apologies- this may be the wrong forum for this type of question but I'm tearing my hair out.
The problem - I have a flat excel workbook that has within it data similar to the structure below:
| ID | Statement 1 | Statement 2 | Statement 3 | Statement 4 |
| 1 | Agree | Strongly Agree | Neutral | Disagree |
| 2 | Disagree | Strongly Disagree | Neutral | Neutral |
| 3 | Strongly Agree | Neutral | Disagree | Agree |
I'm able to easily generate pivot tables for each statement ( 1,2,3 etc) to show how many "agree's", "disagree's" etc there are.
However, what I want is a pivot table that counts the number of "Strongly agree's","agree" etc against each statement so that I can get a result something like below:-
| Statement 1 | Statement 2 | Statement 3 | |
| Strongly Agree | 1 | 1 | 0 |
| Agree | 1 | 0 | 0 |
| Neutral | 0 | 1 | 2 |
| Disagree | 1 | 0 | 1 |
| Strongly Disagree | 0 | 1 | 0 |
I thought this would be a very simple thing to do, but I cant get it to work. Can anyone help at all, or point me in a direction where I could get help?
Thanks in advance
Phil
philbloggs
Detlef_Lewin is rereferring to using Power Query to Unpivot the data first, this then converts your multiple columns into a 2 columns as per the attached file. This is then ideal for Pivot Table use.
Click inside your data and turn it into a Table (using Ctrl T) then go to Data - From Table/Range (or it may say from Sheet depending on your version of Excel)That opens Power Query
Then Right CLick the first column and choose Unpivot Other then click Close and Load To... drop down and choose Data Model connection only
5 Replies
philbloggs
Detlef_Lewin is rereferring to using Power Query to Unpivot the data first, this then converts your multiple columns into a 2 columns as per the attached file. This is then ideal for Pivot Table use.
Click inside your data and turn it into a Table (using Ctrl T) then go to Data - From Table/Range (or it may say from Sheet depending on your version of Excel)That opens Power Query
Then Right CLick the first column and choose Unpivot Other then click Close and Load To... drop down and choose Data Model connection only
Wyn Hopkins That's really helpful. Thanks for taking time out to answer such a rookie question.
Phil
Your data is already in a crosstabular structure. You have to unpivot first before you can do the pivot table.
- Thanks for getting back to me so quickly.
The raw data isn’t in a pivot chart- it’s a the output from a MS forms questionnaire as an excel spreadsheet.
Phil- It is still a crosstabular structure.
