Pivot Table, filter questionnaire data by Region, Business Unit, and Question, PLEASE HELP

Hi,

This is my first post on here and I'm really hopeful about finding a solution for a problem I've been trying to figure out for days now. I have a spreadsheet containing the results of an employee questionnaire, the columns across the top are "Region" (US or EU) "Business Unit" (Marketing, Accounting etc), and then the rest of the column headings represent each question in the questionnaire, there are 20 questions in the survey so there are 22 headings in this spreadsheet. Each question had 5 different possible answers, they were "Strongly Agree", "Agree", "Neither Agree nor Disagree", "Disagree" and "Strongly Disagree". Employees had to choose one per question, so under each question column there is a mixture of these 5 answers.

What I need to be able to do is to create a pivot table, with slicers, which would be linked to a bar chart. I would have 3 slicers, Region, Business Unit, and Question (which would contain a list of the 20 questions). I need to be able to filter by these 3 things. I don't know if it's possible to create a slicer for "Question" as the 20 question names are spread over 20 column headers. On the bar chart I need to be able to display the levels of "Strongly Agree", "Agree", "Neither Agree nor Disagree", "Disagree" and "Strongly Disagree" for the Region, Business Unit, or Question(s) depending on what filters the user clicks on.

I've been trying to create this for the past few days but the closest I've come is to create a sheet with headers called "Questions", "Strongly Agree", "Agree", "Neither Agree nor Disagree", "Disagree" and "Strongly Disagree", then I listed each question underneath the "Questions" heading and placed the a count of the number of "Strongly Agree", "Agree", "Neither Agree nor Disagree", "Disagree" and "Strongly Disagree" responses for each question along in the row beside them. I'm able to filter by question this way, but I don't know how link this data with the Region/Business Unit data.

One thing I was considering was to have 20 rows for each employee, so if the first employee was from the US and the second was from the EU then rows 1-20 would have "US" and 21-40 would have "EU", and the each question would have it's on row under the column "Question".

But this survey is for close to 1,000 people which means manually inputting data in 20,000 rows

Any help with this would be amazing!!!

Hi,

I think a Pivot with Slicers is a good idea.

Can you post an example of your progress so far?

Thanks