Employee survey analysis

[DaddyO72]

Hello,
For years I have googled and searched how to analyze employee exit survey data, in a PivotTable if possible. I finally decided to ask the experts on this forum.
I have a number of characteristics for each employee who answers the survey, like age range, area, division, etc.... I then ask about 10 questions with the employee rating the top 3 answers for leaving (see last table below).

When I do a PivotTable, I want to find how many employees said 1, 2 or 3 for the first question...1, 2, or 3 for the second question, etc... and put it in a chart. I build the PivotTable (see table below...sorry for reversing the tables) but it is missing values. I put the questions in the rows field, and used the first question as the value, but it doesn't always have all the values. Any idea how to do this correctly? I assume this would be the same issue for a survey with a likert scale and trying to determine how many agrees, etc... are in each question. TIA

	Reason for leaving...bad boss	Reason for leaving...low pay	Reason for leaving...promotion	Etc...
1s	16	8	6
2s	12		14
3s		12	2

Employee Name	Age Range	Area	Division	Reason for leaving...bad boss	Reason for leaving...low pay	Reason for leaving...promotion	Etc...
Jane Doe	35-44	EMEA	Sales	1	3		2
John Doe	18-24	NAR	Purchasing		2	3	1
Etc...

 

[ajetrumpet]

I have never used a pivot table in my life, but this made me a bit curious. I do not have a direct solution for you, but perhaps you can find some insight here?

Pivot Table Data to Chart

 

[DaddyO72]

I have never used a pivot table in my life, but this made me a bit curious. I do not have a direct solution for you, but perhaps you can find some insight here?

Pivot Table Data to Chart

Thanks so much, Adam! I use Pivot Tables regularly and it is a little more complicated than this. I was actually able to find the answer at

. Hope this helps the next person.
David

 

[ajetrumpet]

great! good luck to you!