Pivot Table for Analyzing Survey Results by Manager

[GregStewartPTC]

I have been helping a co-worker convert survey results into a workable spreadsheet. Now that all the raw data is compiled I would like to have a pivot table to do a few things for me, but alas, pivot tables are my Achilles heel. I've attached an example of the raw data I am working with. I'm needing a few things:

A slicer to select the manager for which results will be displayed
Answers converted to a numerical value from 1 - 4 (Strongly Disagree, Disagree, Agree, Strongly Agree)
The final output I am needing to be broken into categories based on the questions, and a numerical average from the answers associated with the questions. Note in the example sheet how each question is color coded with a title for each section (Information, Resources, etc).
Ideally I would like to click on the manager name in the slicer and see a table that outputs each of those titles with the numerical average displayed in the cell right below:

Information
3.5

Our corporation is intending to deploy these surveys on a bi-yearly basis, so I would appreciate any instructions that can be given so that I may complete this solo in the future.
Thank you in advance for any help.

[dflak]

The key to any exercise with pivot tables is to get the data in what I call "denormalized" format. It's the opposite of how you want to organize a database. In a database, you can have a bookcase with several shelves and each shelf has a different number of books on it. So you set it up with one table with the shelf names (A, B and C) and another table with the book names and link the book names to what shelf they are on. For pivot tables every book / shelf combination is enumerated. So it is with this data. The Manager Name is the shelf and the ratings are the books.

So you need to get the data from the format you have to Manager | Area | Question | Rating as shown on the data sheet. I also have a helper column to translate the rating to its numerical equivalent. Two sample pivot tables are shown on the Pivot sheet. With the data in this format you can do almost anything with the pivot tables.

For the next use, clear out the information from row 3 down on the Input Sheet. Then copy and paste in the new data to cell A2. As long as the questions and areas remain the same, you are good to go. Otherwise, it's probably a minor tweak to fix the code to match the sheet.

Run the macro, parsedata and refresh the pivot table. ParseData clears out the data table and shuffles the new data into it.

[GregStewartPTC]

This is outstanding. Not the first time you've saved the day for me either. You made this very easy to understand, and your macro is even easy to understand if I would ever need to make some tweaks. I would give you reputation but apparently you are the last user I gave rep to, and it is preventing me from doing so again.

Thank you very much for the timely response. I really do appreciate it. Have a great day!

[dflak]

Thanks for the kind words and good luck with the project.

[abdawson]

This was a great solution. Is there a way where I can arrange the pivot table which would give me result of percentage of each option i.e Strongly Agree to Strongly Disagree for all questions separately by manager?

[dflak]

Ah, the beauty of normalized data and pivot tables. Once you have the data in this format, you can do almost anything with it.

I added another pivot table on the Pivot 1 sheet. This one summarizes the data by percent of row.

The Manager Slicer is hooked up so it controls all the pivot tables on the sheet.