Trouble genrating correct pivot table data from a survey

[Chrispelletier]

Hello,

I have some experience working with pivot tables but I am now trying to work with data generated by a survey and have not be able to figure out how to format my pivot table correctly. Here is my situation:

I have survey data where more than 1 clients where asked to rate the same employee on their client service on numerous questions. The ratings can be: strongly agree/agree/disagree/strongly disagree. What I would like to do is have a pivot table that would show, in percentage, how many times an employee received a specific rating on all the questions (I am able to provide the correct pivot table for 1 question but have not been able to do that for all the questions.)

I am attaching a excel worksheet that shows you an example of the data that I have, my current pivot table (only showing results of 1 question) and an example of what I would like my pivot table to look like.

Thank you for your time.

[Olly]

You need to unpivot your data, so that Question becomes ONE field, rather than four separate fields:

	A	B	C
1	Employee	Question	Value
2	EmployeeA	Question1	Strongly agree
3	EmployeeA	Question1	Strongly agree
4	EmployeeA	Question2	Agree
5	EmployeeA	Question2	Agree
6	EmployeeA	Question3	Strongly agree
7	EmployeeA	Question3	Strongly agree
8	EmployeeA	Question4	Agree
9	EmployeeA	Question4	Agree
10	EmployeeB	Question1	Strongly Disagree
11	EmployeeB	Question1	Strongly Disagree
12	EmployeeB	Question2	Disagree
13	EmployeeB	Question2	Disagree
14	EmployeeB	Question3	Strongly Disagree
15	EmployeeB	Question3	Strongly Disagree
16	EmployeeB	Question4	Disagree
17	EmployeeB	Question4	Disagree
18	EmployeeC	Question1	Strongly agree
19	EmployeeC	Question1	Strongly agree
20	EmployeeC	Question2	Agree
21	EmployeeC	Question2	Agree
22	EmployeeC	Question3	Disagree
23	EmployeeC	Question3	Disagree
24	EmployeeC	Question4	Strongly Disagree
25	EmployeeC	Question4	Strongly Disagree

Once you've done that, then you should be able to figure out your pivot table for all questions.

To unpivot your data:
1. Press Alt-D-P to open the Pivot Table Wizard dialog.
2. Choose 'Multiple consolidation ranges', click Next.
3. Choose 'I will create the page fields'
4. Enter your data range (in this case; Sheet1!$A$3:$E$9), click Add, click Next
5. Choose a location for your pivot table, click Finish.
6. Where the grand total row and column intersect, double click this overall grand total number.

Hey presto - an unpivoted table of data appears (as posted above), which you can now use as the source of your report

[Chrispelletier]

Thank you Olly for your quick response. First time I have to un-pivot my data but it does make sense with what you showed above. I wiil work on my own data in upcoming days but will mark as solved right away. Thanks again!

[ammartino44]

That's cool. How does that work without changing the source data?